How to Group by in SQL Server

Question

I have this data.

 Server | Server Address    | Site |    Timeslot     | Status  |
   X      www.X.example.com    1A    1/11/16 10:00AM   Critical
   X      www.X.example.com    1A    1/11/16 10:01AM   Critical
   X      www.X.example.com    1A    1/11/16 10:02AM   Critical
   Y      www.Y.example.com    1A    1/11/16 10:01AM      OK
   Y      www.Y.example.com    1A    1/11/16 10:02AM      OK
   Y      www.Y.example.com    1A    1/11/16 10:03AM      OK
   X      www.X.example.com    2A    1/11/16 10:01AM   Critical
   X      www.X.example.com    2A    1/11/16 10:02AM   Critical
   X      www.X.example.com    2A    1/11/16 10:03AM   Critical
   Y      www.Y.example.com    2A    1/11/16 10:01AM      OK
   Y      www.Y.example.com    2A    1/11/16 10:02AM      OK
   Y      www.Y.example.com    2A    1/11/16 10:03AM      OK
   X      www.X.example.com    1A    1/12/16 10:31AM      OK
   X      www.X.example.com    1A    1/12/16 10:32AM      OK
   X      www.X.example.com    1A    1/12/16 10:33AM      OK
   Y      www.Y.example.com    1A    1/12/16 10:31AM   Critical
   Y      www.X.example.com    1A    1/12/16 10:32AM   Critical
   Y      www.X.example.com    1A    1/12/16 10:33AM   Critical
   X      www.X.example.com    2A    1/12/16 10:31AM      OK
   X      www.Y.example.com    2A    1/12/16 10:32AM      OK
   X      www.Y.example.com    2A    1/12/16 10:33AM      OK
   Y      www.Y.example.com    2A    1/12/16 10:31AM   Critical
   Y      www.Y.example.com    2A    1/12/16 10:32AM   Critical
   Y      www.Y.example.com    2A    1/12/16 10:33AM   Critical

And I need to COUNT the number of CRITICAL per SERVER and divide it with different SITE and distinguish what time does the server became critical. Like this.

Server | S-1A Critical |    Timeslot     | S-2A Critical |    Timeslot     |
  X            3         1/11/16 10:00AM         3         1/11/16 10:00AM
  Y            3         1/12/16 10:30AM         3         1/12/16 10:30AM

Somebody help me with this one.. I already gone through different questions here regarding about these problem. but I don't quite understand. Your response is highly much appreciated.

Why do you need 2 Timeslot columns in your final result set? — Sunil, Jan 12 '16 at 03:04
He wants to push the Site out column wise, grouped row wise on the Server field, and that value is the MIN TimeSlot that the pivoted Site became Critical. — Ross Bush, Jan 12 '16 at 03:07
@Irb, then only one TimeSlot column is needed. Why 2 same columns? — Sunil, Jan 12 '16 at 03:12
@Irb, I get it. First timeslot column is for `S1-A` while second is for `S2-A`. — Sunil, Jan 12 '16 at 03:13
I am under the impression that the first TimeSlot belongs to "S-1A Critical" and the second belongs to "S-2A Critical". It may just be a typo or coincidence that they became critical at the same time...if I understand the question correctly. — Ross Bush, Jan 12 '16 at 03:14
Possible duplicate of [Group By Multiple Columns](http://stackoverflow.com/questions/847066/group-by-multiple-columns) — Mukesh Kalgude, Jan 12 '16 at 03:50

Quantumplate · Answer 1 · 2016-01-12T03:30:06.370

You could try something like this...

select 
  Server, 
  SUM(CASE WHEN Site = '1A' AND Status = 'Critical' THEN 1 ELSE 0 END) as 'S-1A Critical', 
  MIN(CASE WHEN Site = '1A' AND Status = 'Critical' THEN Timeslot ELSE GETDATE() END) as Timeslot1A, 
  SUM(CASE WHEN Site = '2A' AND Status = 'Critical' THEN 1 ELSE 0 END) as 'S-2A Critical', 
  MIN(CASE WHEN Site = '2A' AND Status = 'Critical' THEN Timeslot ELSE GETDATE() END) as Timeslot2A
from TableName
group by Server

It assumes there are just two values for Site and that timeslot is always less than GETDATE().

Sunil · Answer 2 · 2016-01-12T03:45:04.213

0

Use the query as below. I am assuming that your table name is Servers.

SELECT Server, 
  SUM(case when Site = '1A' and Status= 'Critical' then 1 ELSE 0 END) as [S-1A Critical],
  MIN (case when Site = '1A' and Status= 'Critical' then Timeslot end ) as [S-1A TimeSlot],
  SUM(case when Site = '2A' and Status= 'Critical' then 1 ELSE 0 END) as [S-2A Critical],      
  MIN (case when Site = '2A' and Status= 'Critical' then Timeslot end ) as [S-2A TimeSlot]
 from Servers group by Server;

edited Jan 12 '16 at 03:45

answered Jan 12 '16 at 03:16

Sunil

20,653
28
112
197

You have typos in your answer (ESLE), Site=1 should be ='1A' and your SUM is counting both Critical and OK. – Quantumplate Jan 12 '16 at 03:23
ha, I left it off some of mine too! – Quantumplate Jan 12 '16 at 03:32
it's not a simple query so its easy to miss out without intellisense. – Sunil Jan 12 '16 at 03:34

Joel Coehoorn · Accepted Answer · 2016-01-12T04:12:27.563

No case statements:

WITH Crits AS
(
    SELECT Server, Site, MIN(Timeslot) As Timeslot, COUNT(*) As SiteCount
    FROM [table]
    WHERE Status = 'Critical'
    GROUP BY Server,Site
)
SELECT coalesce(c1.Server, c2.Server) Server, 
      c1.SiteCount As [S-1A Critical], 
      c1.Timeslot as [S-1A TimeSlot], 
      c2.SiteCount As [S-2A Critical],  
      c2.Timeslot as [S-2A Timeslot]
FROM 
( SELECT SERVER FROM Crits WHERE Site = '1A' GROUP BY Server ) c1
FULL JOIN 
( SELECT SERVER FROM Crits WHERE Site = '2A' GROUP BY Server ) c2 
    ON c2.Server = c1.Server

score 0 · Answer 4 · answered Jan 12 '16 at 05:47

I would just do something like this, although it's not precisely the same format as your example output:

SELECT
   Server,
   Site,
   NumCriticals = COUNT(*),
   Timeslot = MAX(Timeslot)

FROM Servers

WHERE Status = 'Critical'

GROUP BY Server, Site
ORDER BY Server, Site;

Although, I have a feeling you would only want servers listed that are presently critical, which this won't accomplish.

How to Group by in SQL Server

4 Answers4