SQL Server statement output

Question

I have following table structure

TicketID    Status  Duration
-----------------------------
1234           8        2
1233           8       10
1232           4        5
1231           8       12
1230           4       50

status 8 means Closed
status 4 means Open

It is required to have output in following way. Please do the need ful. If possible I wanted it in a single sql query.

Please help me to produce output in following way.

Status  Closed (sum)    Open(Sum)
---------------------------------
   8       24              0
   4        0             55

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Dec 04 '14 at 06:52
Did you try anything at all ? Read http://msdn.microsoft.com/en-us/library/ms177673.aspx and http://msdn.microsoft.com/en-us/library/ms177673.aspx — Mahesh, Dec 04 '14 at 06:56
@SandipkTatva You might be looking for `GROUP BY` (links posted in the comments above), or `PIVOT` if your actual case is more complex than what you posted (i.e. there are more statuses in reality, etc) http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx and http://stackoverflow.com/questions/24470/sql-server-pivot-examples — Ruslan, Dec 04 '14 at 07:45

score 0 · Answer 1 · answered Dec 04 '14 at 06:57

A case expression could filter out the unwanted values, so this can be done in a single aggregate query:

SELECT   status, 
         SUM (CASE status WHEN 8 THEN duration ELSE 0 END) AS closed_sum,
         SUM (CASE status WHEN 4 THEN duration ELSE 0 END) AS open_sum
FROM     my_table
GROUP BY status

score 0 · Answer 2 · answered Dec 04 '14 at 07:00

0

select status
       , sum( case when status = 4 then Duration else 0 end ) as [Open(sum)]
       , sum( case when status = 8 then Duration else 0 end ) as [Closed(sum)]
from @test
group by status
order by status desc

answered Dec 04 '14 at 07:00

Deepshikha

9,896
2
21
21

score 0 · Answer 3 · answered Dec 04 '14 at 07:38

0

WITH T AS (SELECT Status, SUM(Duration) AS Duration FROM Table1 GROUP BY Status)
SELECT Status, Duration, 0 FROM T WHERE Status = 8
UNION ALL
SELECT Status, 0, Duration FROM T WHERE Status = 4

answered Dec 04 '14 at 07:38

CrimsonKing

2,696
1
14
11

To produce the EXACT expected output. I am aware that the WITH part gives all the information needed. The UNION part is to produce the EXPECTED output in the question. See the format in the question. – CrimsonKing Dec 04 '14 at 07:56

Dudi Konfino · Answer 4 · 2014-12-04T08:43:01.967

0

use union all

    SELECT [Status],sum([Duration]) [Closed(sum)], '0' as   [Open(Sum)]
             from [dbo].[tblx] 
             where [Status]=8
             GROUP BY [Status] 


UNION all            
              SELECT [Status], '0' as [Closed(sum)],sum([Duration]) [Close(sum)]
             from [dbo].[tblx] 
             WHERE [Status]=4
             GROUP BY [Status]

enter image description here

edited Dec 04 '14 at 08:43

answered Dec 04 '14 at 08:22

Dudi Konfino

1,126
2
13
24

SQL Server statement output

4 Answers4