SQL - Modify Query to create Columns

Question

I currently have this query. Which used to work for my requirements.

 SELECT sites.sitename,
   severity.severity,
   COALESCE(Count(vulns.id), 0) AS Totals
FROM   sites
   INNER JOIN systems
           ON sites.id = systems.siteid
   CROSS JOIN severity
   LEFT JOIN vulns
          ON vulns.systemid = systems.id
             AND vulns.risk_factor = severity.severity
GROUP  BY sites.sitename,
      severity.severity 

And this returns results like

SiteName | Severity | Totals
Orlando  | Red      | 0
Orlando  | Yellow   | 1
Orlando  | Green    | 22
Orlando  | Orange   | 1321
Tampa    | Red      | 22
Tampa    | Yellow   | 111
Tampa    | Green    | 223
Tampa    | Orange   | 121

How can I modify this query to break out the severity into columns. Such as

SiteName | Red | Yellow | Green | Orange
Orlando  | 0   | 1      | 22    | 1321

Answer 1

You can use conditional aggregation:

 SELECT sites.sitename,   
        Count(CASE WHEN severity.severity = 'Red' THEN vulns.id END) AS Red,
        Count(CASE WHEN severity.severity = 'Yellow' THEN vulns.id END) AS Yellow,
        Count(CASE WHEN severity.severity = 'Green' THEN vulns.id END) AS Green,
        Count(CASE WHEN severity.severity = 'Orange' THEN vulns.id END) AS Orange    
FROM   sites
   INNER JOIN systems
           ON sites.id = systems.siteid
   CROSS JOIN severity
   LEFT JOIN vulns
          ON vulns.systemid = systems.id
             AND vulns.risk_factor = severity.severity
GROUP  BY sites.sitename

Note: You don't need COALESCE since COUNT(NULL) returns 0 anyway.