Monthly Cost, Bar Chart- SQL

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:
month(period_start_date) as Mesi,
sum(line_item_cost) AS cost
from CUR_Table
user_cliente = ‘MCS’
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?


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.

	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


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