Group By Year and Month in Metabase

Hi, everyone

How do I group by month and year in a native SQL query. It sounds stupid, but here is the problem:

I have the following Query:

    SELECT MONTHNAME(creation_timestamp) AS month, COUNT(*)
FROM akten
WHERE testakte !="Ja"
    AND name NOT LIKE '%test%'
    AND groupname = "GARA"
    AND motorenbezeichnung = "EA 189"
    AND bearbeitungsschema = "vw"
    AND deletion_timestamp IS NULL
    [[AND {{zeitspanne}}]]
    
GROUP BY month
ORDER BY MONTH(creation_timestamp)

And and it works fines when the time range does not exceed one year. However, I’d like to stretch the query over multiple years and whenever I do this (or try to do this) with the following Query:

SELECT YEAR(creation_timestamp) AS Jahr, MONTHNAME(creation_timestamp) AS Monat, COUNT(*)
FROM akten
WHERE testakte !="Ja"
    AND name NOT LIKE '%test%'
    AND groupname = "GARA"
    AND motorenbezeichnung = "EA 189"
    AND bearbeitungsschema = "vw"
    AND deletion_timestamp IS NULL
    [[AND {{zeitspanne}}]]
    
GROUP BY Jahr, Monat
ORDER BY Jahr, Monat

When I try to create a visualisation in Metabase, I either have to chose the “Monat” or the “Jahr” column - if I add a second series to the X-Axis, the results become stack.

Is there a way to bind both columns togehter?

Hi, hm, yes I struggle with that a lot :smiley: .

What I do is the following:

concat(created_at_year, " - ", created_at_month) as Yearmonth

Then you can group by Yearmonth, the order by would still have to be by DATE (not even month or year, otherwise - at least for me - the order is wrong.

My example query from just today:

select concat(donation_created_at_month_local," - ", donation_created_at_day_local) as Spendenmonat_Tag, donation_created_at_date_local, sum(amount_in_cents/100) as Spendensumme_EUR
from table1
where 1=1
group by Spendenmonat_Tag
order by donation_created_at_date_local

Hope that helps!

1 Like

This seems to work!

Vielen Dank, @EvaS!