Hello everyone, I am new on grafana.
I am struggling to deliver to my boss a graph (like a Histogram or a Bar Chart), that shows the monthly cost of last 12 months timeframe.
The problem is that some months , there is no cost, it should return zero.
My basic query is:
SELECT
month(period_start_date) as Mesi,
sum(line_item_cost) AS cost
from CUR_Table
where
user_cliente = ‘MCS’
and
$__timeFilter(bill_billing_period_start_date)
group by 1
order by 1
It returns me one single month, acctualy the current month.
I need it to show me 12 bars, even if some of them is 0.
Could you please help me guys?
Hello,
Is this microsoft sql server or mysql or postgres?
1 Like
Hi Yiosias,
I am using AWS athena. As i found in its documentation: “Athena DML is based on [Presto 0.172]”.
Does this answer your question ?
Many thanks
I am not familiar with AWS Athena syntax but you want a list of numbers table you can use to left join into your real table. The following is in SQL Server. So your question is really more AWS athena than it is grafana unless there is a way of filling gaps in grafana.
;WITH cteN AS
(
select 1 as Number UNION
select 2 UNION
select 3 UNION
select 4 UNION
select 5 UNION
select 6 UNION
select 7 UNION
select 8 UNION
select 9 UNION
select 10 UNION
select 11 UNION
select 12
), ctesum as (
SELECT month(ct.period_start_date) as Mesi,
sum(ct.line_item_cost) AS cost
from CUR_Table ct
where user_cliente = 'MCS'
--and $__timeFilter(bill_billing_period_start_date)
group by month(period_start_date)
)
select n.Number as month, isnull(c.cost,0.00)
from cteN n
left join ctesum c on n.Number = c.Mesi
It generates the following barchart
histogram
1 Like
Dear yosiasz, so many thanks!
It works perfectly!
I am very happy and grateful for such kindness!!
Thank you very much for spending your time to help me. Its amazing.
God bless you!
1 Like
