Skip to content
Advertisement

Query with group by with CustID and amounts daily and MTD

I have the following data:

ID username Cost time
1 test1 1 2021-05-22 11:48:36.000
2 test2 2 2021-05-20 12:55:22.000
3 test3 5 2021-05-21 00:00:0-0.000

I would like to count the costs for the username with a daily figure and a month to date figure in once table

I have got the following

This will return the monthly figures and changing the time to ‘2021-05-22 00:00:00’} AND {ts ‘2021-05-22 23:59:59’} will give the the daily however I would like a table to show daily and MTD together

username Daily MTD
test1 1 1012
test2 2 500
test3 5 22

Any help or pointers would be fantastic, I am guessing that I need a temp table and then run again using the MTD range updating the temp table where the username is the same then to export and delete – but i have no idea where to start.

Advertisement

Answer

First, you need one row per user and date, not just one row per user.

Second, you should fix your date arithmetic so you are not missing a second.

Then, you can use aggregation and window functions:

You can learn more about window functions in a SQL tutorial (or, ahem, a book on SQL) or in the documentation.

EDIT:

If you only want the most recent date and MTD, then you can either filter the above query for the most recent date or use conditional aggregation:

And, you can actually express the query using the current date so it doesn’t have to be hardcoded. For this version:

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement