OCI_COST_DATA_MV-2.sql

--my cost by service
SELECT SUBSCRIPTION_ID, --region, compartment_name,
       MIN(INTERVAL_USAGE_START) INTERVAL_USAGE_START, MAX(INTERVAL_USAGE_END) INTERVAL_USAGE_END,
       service_name,
       SUM(MY_COST) MY_COST,CURRENCY_CODE
FROM OCI_COST_DATA_MV
WHERE 1=1
--and service_name in ( 'DATABASE','COMPUTE','NETWORK')
--and compartment_name = 'apex-atp'
AND my_cost != 0
GROUP BY SUBSCRIPTION_ID, service_name, CURRENCY_CODE
ORDER BY 5 DESC

-- cost by compartment
SELECT SUBSCRIPTION_ID, compartment_name, --region,
       MIN(INTERVAL_USAGE_START) INTERVAL_USAGE_START, MAX(INTERVAL_USAGE_END) INTERVAL_USAGE_END,
       --service_name,
       SUM(MY_COST) MY_COST,CURRENCY_CODE
FROM OCI_COST_DATA_MV
WHERE 1=1
--and service_name in ( 'DATABASE','COMPUTE','NETWORK')
--and compartment_name = 'apex-atp'
AND my_cost != 0
GROUP BY SUBSCRIPTION_ID, compartment_name, CURRENCY_CODE
ORDER BY 5 DESC

-- individual resource's cost details
SELECT SUBSCRIPTION_ID, region, compartment_name, RESOURCE_ID,
       MIN(INTERVAL_USAGE_START) INTERVAL_USAGE_START, MAX(INTERVAL_USAGE_END) INTERVAL_USAGE_END,
       PRODUCT_SKU, PRODUCT_DESCRIPTION, BILLING_UNIT_READABLE, SUM(BILLED_QUANTITY) BILLED_QUANTITY,
       SKU_UNIT_DESCRIPTION, UNIT_PRICE,
       SUM(MY_COST) MY_COST,CURRENCY_CODE
FROM OCI_COST_DATA_MV
WHERE 1=1
--and service_name in ( 'DATABASE','COMPUTE','NETWORK')
--and compartment_name = 'apex-atp'
--and to_char(interval_usage_start) like '11-APR-23%'
AND my_cost != 0
GROUP BY region, compartment_name, RESOURCE_ID,
SUBSCRIPTION_ID, PRODUCT_SKU, PRODUCT_DESCRIPTION, BILLING_UNIT_READABLE, SKU_UNIT_DESCRIPTION, UNIT_PRICE,CURRENCY_CODE
ORDER BY 5,PRODUCT_SKU