Order by Date, then sort by other field in SQL Server-CodePudding

I'm trying to sort by Date and group by another field. Seems challenging because ORDER BY and GROUP BY clauses use the same fields for aggregations.

This is some example data:

Date	Operation	Count
2021-10-13 9:12:00	Visits	2
2021-10-13 8:11:00	Calls	1
2021-10-13 7:10:30	Calls	3
2021-10-13 6:00:00	Calls	5
2021-10-13 5:10:00	Visits	2
2021-10-13 4:00:00	Visits	1
2021-10-12 3:20:00	Calls	2
2021-10-12 2:10:00	Calls	2
2021-10-12 1:00:00	Visits	2

I need to show groups of "Visits" and "Calls", on different days. The result should be:

Date	Operation	Count
2021-10-13	Visits	2
2021-10-13	Calls	9
2021-10-13	Visits	3
2021-10-12	Calls	4
2021-10-12	Visits	2

Right now, I've tried:

SELECT
    CAST([Date] AS DATE) [Date],
    Operation,
    SUM([Count])
FROM Table
GROUP BY CAST([Date] AS DATE), Operation
ORDER BY CAST([Date] AS DATE) DESC, Operation

But it gives the following result:

Date	Operation	Count
2021-10-13	Calls	9
2021-10-13	Visits	5
2021-10-12	Calls	4
2021-10-12	Visits	2

Here's a fiddle to make working with this easier:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=39bb598378b35603ca98c0c4733d8f92

I'm thinking now in adding a temporary table with an additional column called "Group", but I'm not sure if I can try a better solution. I've seen answers to similar problems, but the "Date" problem seems to be different

Could you share ideas?

CodePudding user response：

I'd probably wind up using a CTE (there's probably a way to do it similar to how you've already tried though). Something along the lines of:

WITH x AS(
    SELECT CAST([Date] AS DATE) [Date],
           Operation,
           SUM(t.[Count]) as [Count]
    FROM [MyTable]
    GROUP BY CAST([Date] AS DATE),
             Operation )
SELECT x.[Date], 
       x.Operation,
       x.[Count]
FROM x AS x
ORDER BY x.[Date] desc,
         x.Operation;

Although; if you're going down the route of wanting to show something like:

X calls came before Y visits, then there were another z calls before the end of the day.

Then you'll need something more custom, like the solutions that were linked in in the comments.

CodePudding user response：

As discussed this is gaps/islands problem which just requires isolating the additional distinct groups, using a running count and subtracting a count partitioned by each group of rows:

with grp as (
    select Convert(date, [date]) [Date], operation, [count], 
        Row_Number() over(order by [date]) 
        - Row_Number() over(partition by operation, Convert(date, [date]) order by date) gp
    from t
)
select date, operation, Sum([count]) [Count]
from grp
group by [date], operation, gp
order by [date] desc, gp desc

DB Fiddle