SQL Count multiple columns based

Question

I have 2 data tables I need to query data from

Machine_ID  Machine_name
101         Computer1
202         Computer2
303         Computer3
404         Computer4

Second table:

Machine_ID Event_ID Event_date
101        1        03/15/2016
101        1        03/14/2016
101        2        03/15/2016
101        1        03/13/2016
102        1        03/15/2016
102        2        03/15/2016
102        1        03/14/2016
103        2        03/15/2016
103        2        03/14/2016
104        1        03/15/2016

I need the output of the query to be in one line and contain information:

Machine_ID, 
Machine_Name, 
Count(Machine_ID) where Event_ID='1'
Count(Machine_ID) where Event_ID='2'

I have tried numerous ways to write the query but am at a loss for the correct syntax.

This does not work but will give an idea of what I'm going for:

Select M.Machine_ID, M.Machine_Name, 
count(Machine_ID) where E.Event_ID='1' and Machine_ID='101', 
count(Machine_ID) where E.Event_ID='2' and Machine_ID='101' 
from Machines M 
join Events E on M.Machine_ID = E.Machine_ID

The output from this based in the given data should be:

101 Computer1 3 1

Possible duplicate of [How to get multiple counts with one SQL query?](http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — Tab Alleman, Mar 22 '16 at 20:47

score 0 · Accepted Answer · answered Mar 22 '16 at 20:50

0

Try this:

SELECT 
    M.Machine_ID, 
    M.Machine_Name, 
    COUNT(CASE WHEN E.Event_ID='1' THEN 1 ELSE NULL END) AS Event1,
    COUNT(CASE WHEN E.Event_ID='2' THEN 1 ELSE NULL END) AS Event2
FROM Machines M 
    JOIN Events E 
    ON M.Machine_ID = E.Machine_ID
GROUP BY M.Machine_ID, M.Machine_Name

answered Mar 22 '16 at 20:50

morgb

2,252
2
14
14

You don't need to specify ELSE NULL. This is default behavior for CASE statement. – Kamil Gosciminski Mar 22 '16 at 20:51
Procedural programmers sometimes freak out because of all the coding standards requiring and `else`. I often include just to keep them happy even though I would prefer to omit it most of the time. – shawnt00 Mar 22 '16 at 21:00
@ConsiderMe Since it doesn't hurt anything, I prefer to be explicit where possible rather than leave any decisions to the computer. Computers do dumb things in the absence of instruction. But it's just a personal preference/opinion. – morgb Mar 22 '16 at 21:42
But there is an instrunction, just not explicitly wrote by the user. Anyways, it's a matter of preference I guess as you've said :) – Kamil Gosciminski Mar 22 '16 at 21:44
Well this worked for what I wanted to do. I just never considered using a case statement for doing the counting. – Ryan257 Mar 25 '16 at 13:37

score -1 · Answer 2 · answered Mar 22 '16 at 20:53

Use a conditional aggregation:

SELECT 
    M.Machine_ID, 
    M.Machine_Name, 
    SUM(CASE WHEN E.Event_ID = '1' THEN 1 END) AS Event1_cnt,
    SUM(CASE WHEN E.Event_ID = '2' THEN 1 END) AS Event2_cnt
FROM 
    Machines M 
    JOIN Events E 
      ON M.Machine_ID = E.Machine_ID
GROUP BY 
    M.Machine_ID, M.Machine_Name

If you want to limit the output only to one machine id (for instance: 101) add a following line before GROUP BY:

WHERE M.Machine_ID = '101'

Also, it seems like a bad idea to store ID as a string. Consider changing your data type to integer or anything alike.

Could the person who downvoted this answer add a comment on why has this action been taken? — Kamil Gosciminski, Mar 23 '16 at 17:28

SQL Count multiple columns based

2 Answers2