3

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.

AS91
  • 527
  • 7
  • 18
  • It does beg the question as to why you have those extra duplicate rows in the first place. BTW, good job on asking an understandable question with proper tags. I wish all SQL questions were as clear as yours. – sstan Aug 30 '16 at 02:26
  • Duplication is because I'm pulling data located in 4 different tables for which I use `LEFT JOINS` with 40-50 columns at least in the resulting joined table. – AS91 Aug 30 '16 at 02:33

4 Answers4

4

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

You can use Distinct inside SUM function. It will give you expected result.

SELECT Name, Status, SUM(DISTINCT Duration)
FROM T1
GROUP BY Name,Status
Sagar Shelke
  • 517
  • 3
  • 10
1

You could use CTE, 

WITH C1 AS(
          SELECT Name, Status, Duration 
          FROM Table T1
          GROUP BY Name,Status,Duration
        )
SELECT Name,Status,SUM(Duration) FROM C1 GROUP BY Name,Status
Victor Guan
  • 106
  • 1
  • 1
  • 10
0

with temp_cte as (select Name, Status, Duration FROM dbo.test2 group by name,status,duration ) select tc.name,tc.status,sum(tc.duration) from temp_cte as tc group by tc.name,tc.status order by name

Priya Gopinath
  • 21
  • 3