CODEWITHSUNDEEP
sqlsql-server
Rex
Rex

Reputation: 33

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...

Upvotes: 1

Views: 72

Answers (3)

Rahul
Rahul

Reputation: 77936

You should probably use a conditional count like

SUM( case when alertDate BETWEEN '2016/02/15' AND '2016/02/16' 
     AND UPPER(alertType)='CRITICAL' then 1 else 0 end) as Critical,

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 3

JohnHC
JohnHC

Reputation: 11205

You can use sum(case) for this:

SELECT alertDate as "Alert Date",
    sum(case when UPPER(alertType)='CRITICAL' then 1 end) as Critical,
    sum(case when UPPER(alertType)='WARNING' then 1 end) as Warning
FROM alerts
where alertDate BETWEEN '2016/02/15' AND '2016/02/16'
GROUP BY alertDate;

Upvotes: 2

Related Questions