SQL Query group by for a beginner

Question

I'm quite new to SQL Server and was wondering if it was possible to run a query like this. I have a table with the following fields: USER, WORKSTATION, DURATION (seconds)

Each USER can use multiple workstations and each workstation can be used by multiple USERs.

In the table I have records like:

USER1 - WKS1 - 6500
USER1 - WKS2 - 3600
USER1 - WKS2 - 1200
USER1 - WKS2 - 1200
USER1 - WKS3 - 3600
USER2 - WKS1 - 8500
USER2 - WKS2 - 1000
USER2 - WKS2 - 8000
...
...

I would like a result that groups the user and the most used workstation, example:

USER1 - WKS1
USER2 - WKS2
...
...

As specified in the DURATION field in seconds.

There's a way? I've tried but I think it takes nested queries and I'm not very knowledgeable. Thank you!

@jarlh for User1, Wks1 was used most in duration, for user2, wks2 was used the most. Though it's not clear if they want count, avg, max, sum... — Aaron Bertrand, Feb 03 '22 at 14:45
@AaronBertrand, I realized that seconds after commenting... Sorry. — jarlh, Feb 03 '22 at 14:47
Why did you accept an answer that does not return your expected results: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6b8d53c86cc3ae0daffa7226601e5641? — forpas, Feb 03 '22 at 15:18

score 1 · Answer 1 · answered Feb 03 '22 at 15:03

You can group by USER and WORKSTATION to get the total duration for each combination of these 2 columns and with FIRST_VALUE() window function get the value of WORKSTATION with the top duration for each USER:

SELECT DISTINCT [USER], 
       FIRST_VALUE([WORKSTATION]) OVER (PARTITION BY [USER] ORDER BY SUM([DURATION]) DESC) [WORKSTATION]
FROM tablename
GROUP BY [USER], [WORKSTATION];

See the demo.

Teja Goud Kandula · Answer 2 · 2022-02-03T16:22:56.023

0

with cte as (
    Select User, 
           Workstation, 
           Max(Duration) as Duration
    from TableName
    group by User, 
             Workstation 
) 
select User, Workstation
from cte
order by User, Workstation

edited Feb 03 '22 at 16:22

answered Feb 03 '22 at 14:47

Teja Goud Kandula

1,462
13
26

score 0 · Accepted Answer · answered Feb 03 '22 at 14:49

Think about it like this. You want the user and the workstation on which they spent the longest duration. When it comes to duration, longest is synonymous with maximum. So, we want the maximum duration by user.

SELECT user, MAX(duration) AS max_duration
FROM the_table
GROUP BY user;

This gives us the max duration by user.

Then, we want to identify which workstation had that value (max_duration) in the duration column. So, we use the query above as a filter for another query:

SELECT x.user, x.workstation, x.duration
FROM the_table x
INNER JOIN (SELECT user, MAX(duration) AS max_duration
            FROM the_table
            GROUP BY user) y 
            ON x.user = y.user AND x.duration = y.max_duration;

This gives us the required result.

SQL Query group by for a beginner

3 Answers3