Requesting help with aggregating data by month using sql in metabase from redshift

Get Help
1

Hi Team,

I hope you are doing well,

I am working on aggregating data by month,

Note: I am querying data from Redhsift using Metabase.

Following is my query
(SELECT dataset.value AS value, to_char(dataset.time ::date,'MM/YYYY') AS time, COUNT(id) AS count
FROM dataset
GROUP BY dataset.value, to_char(dataset.time ::date,'MM/YYYY')
ORDER BY dataset.value ASC, to_char(dataset.time ::date,'MM/YYYY') ASC)

UNION ALL

(SELECT dataset_2.value_2 AS x, to_char(dataset_2.time ::date,'MM/YYYY') AS time, COUNT(id) AS count
FROM dataset_2
GROUP BY dataset_2.value_2, to_char(dataset_2.time ::date,'MM/YYYY')
ORDER BY dataset_2.value_2in ASC, to_char(dataset_2.time ::date,'MM/YYYY') ASC)

However the above query converts date into string. Hence the output is not in proper order. Eg: Jan 22, Jan 23, Feb 22, March 23 instead of Jan 22, Feb 22, March 22 and so on.

Requesting your help improve the query so the date aggregated by month is in ascending order. Thanks in advance.

2

The order is the order of each sub-query. Results of the first subquery with the order and results of the second query.
You must apply order on the results of the both query.
(SELECT dataset.value AS value, to_char(dataset.time ::date,'MM/YYYY') AS time, COUNT(id) AS count
FROM dataset
GROUP BY dataset.value, to_char(dataset.time ::date,'MM/YYYY') )

UNION ALL

(SELECT dataset_2.value_2 AS value, to_char(dataset_2.time ::date,'MM/YYYY') AS time, COUNT(id) AS count
FROM dataset_2
GROUP BY dataset_2.value_2, to_char(dataset_2.time ::date,'MM/YYYY')

ORDER BY time

3

Hi Laure,

Thank you for your reply.

It worked however this only orders by month and not be year.

The output I got was Jan/2022, Jan/2023, Feb 2022, Feb/2023.....Instead of Jan 2022, Feb 2022, Jan 2023, Feb 2023 and so on.

Is there a way I can order by both month and year in the above code.

Regards,
Vishal

4

It's normal. You order on a string field (function to_char) .... Make order on date field

5

Hi Laure,

When I use the to_date function instead of to_char - the "time" output is like this. 0175-04-28T00:00:00+10:04:52 instead of month and year.

Here are the screenshots of the two datasets:

Dataset
Dataset1236×609 237 KB

Dataset_2
Dataset_2980×611 233 KB

Desired Output:

Desired Output
Desired Output1085×548 55 KB

I hope this helps and gives more details.

Regards,
Vishal

6

Try this
(SELECT dataset.value AS value, dataset.time as orderTime, to_char(dataset.time ::date,'MM/YYYY') AS time, COUNT(id) AS count
FROM dataset
GROUP BY dataset.value, dataset.time, to_char(dataset.time ::date,'MM/YYYY') )

UNION ALL

(SELECT dataset_2.value_2 AS value, dataset.time as orderTime, to_char(dataset_2.time ::date,'MM/YYYY') AS time, COUNT(id) AS count
FROM dataset_2
GROUP BY dataset_2.value_2, dataset.time, to_char(dataset_2.time ::date,'MM/YYYY')

ORDER BY orderTime

1 Like
7

Thank you Laure. That worked.