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

SELECT 
    username,
    COUNT(*) cost
FROM
    dbo.sent
WHERE
    time BETWEEN {ts '2021-05-01 00:00:00'} AND {ts '2021-05-22 23:59:59'}
GROUP BY 
    username
ORDER BY
    cost DESC;

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.

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:

SELECT username, CONVERT(DATE, time) as dte,
       COUNT(*) as cost_per_day,
       SUM(COUNT(*)) OVER (PARTITION BY username ORDER BY CONVERT(DATE, time)) as mtd
FROM dbo.sent s
WHERE time >=  '2021-05-01' AND
      time < '2021-05-23'
GROUP BY username, CONVERT(DATE, time)
ORDER BY username, dte;

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:

SELECT username,
       SUM(CASE WHEN CONVERT(DATE, time) = '2021-05-22' THEN 1 ELSE 0 END) as cost_per_most_recent_day,
       COUNT(*) as MTD
FROM dbo.sent s
WHERE time >=  '2021-05-01' AND
      time < '2021-05-23'
GROUP BY username
ORDER BY username;

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

SELECT username,
       SUM(CASE WHEN CONVERT(DATE, time) = CONVERT(DATE, GETDATE()) THEN 1 ELSE 0 END) as cost_per_most_recent_day,
       COUNT(*) as MTD
FROM dbo.sent s
WHERE time >=  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND
      time < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
GROUP BY username
ORDER BY username;