Rex Rex - 5 months ago 38
SQL Question

Using multiple Count in a Single Query

I have a query below that i wanna return total number of critical & warning alerts of a day,but problem i have is that when i do it for range of days it's adding the critical alerts of the date specified

SELECT alertDate as "Alert Date",
(SELECT COUNT(alertType) FROM alerts WHERE alertDate BETWEEN '2016/02/15' AND '2016/02/16' AND UPPER(alertType)='CRITICAL') as Critical,
(SELECT COUNT(alertType) FROM alerts WHERE alertDate BETWEEN '2016/02/15' AND '2016/02/16' AND UPPER(alertType)='WARNING') as Warning
FROM alerts
where alertDate BETWEEN '2016/02/15' AND '2016/02/16'
GROUP BY alertDate;


tried the code above but apparently i can't add "group by" clause inside a sub-query,which i think is the problem,Please any help on how i can go about this...

Answer Source

You want conditional aggregation -- the case goes inside the aggregation function. I would write this as:

SELECT alertDate as AlertDate,
       SUM(CASE WHEN UPPER(alertType) = 'CRITICAL' THEN 1 ELSE 0 END) as Critical,
       SUM(CASE WHEN UPPER(alertType) = 'WARNING' THEN 1 ELSE 0 END) as WARNING
FROM alerts a
WHERE alertDate BETWEEN '2016-02-15' AND '2016-02-16'
GROUP BY alertDate;

If you are using MySQL, you can shorted this to:

SELECT alertDate as AlertDate,
       SUM(UPPER(alertType) = 'CRITICAL') as Critical,
       SUM(UPPER(alertType) = 'WARNING') as Warning
FROM alerts a
WHERE alertDate BETWEEN '2016-02-15' AND '2016-02-16'
GROUP BY alertDate;

Note a couple other changes:

  • No subselects are necessary.
  • I removed the space from AlertDate. I'm just not a fan of column aliases that need to be escaped.
  • I replaced the / with - in the date literals -- the hyphens are ISO 8601 compatible.