Amulya Sharma Amulya Sharma - 3 months ago 9
SQL Question

SUM GROUP BY giving undesired result

First 12 rows of Table T1:

Name Status Duration
Todd Active 60
Todd Active 60
Todd Active 60
Todd Schedu 60
Todd Schedu 60
Todd Schedu 120
Todd Schedu 120
Bran Active 30
Bran Active 30
Bran Active 60
Bran No Show 120
Bran No Show 120


If I run this query (or use a DISTINCT without the GROUP BY):

SELECT Name, Status, Duration
FROM Table T1
GROUP BY Name,Status,Duration


I get:

Name Status Duration
Todd Active 60
Todd Schedu 60
Todd Schedu 120
Bran Active 30
Bran Active 60
Bran No Show 120


From the above result, I want the desired result as
SUM(Duration) GROUPED BY Name, Status
:

Name Status Duration
Todd Active 60
Todd Schedu 180
Bran Active 90
Bran No Show 120


I'm trying this query to achieve the desired result:

SELECT Name, Status, SUM(Duration)
FROM Table T1
GROUP BY Name,Status


But I'm getting huge numbers for SUM(Duration) - It's probably adding all the durations and not the distinct durations for each group of Name and Status.

Answer

One method to get what you want uses a subquery:

SELECT Name, Status, SUM(Duration)
FROM (SELECT Name, Status, Duration 
      FROM Table T1
      GROUP BY Name,Status,Duration
     ) NSD
GROUP BY Name, Status;
Comments