I have a table called EventLog like this:

EventLog Table

I would like to display the count of each event type for each deviceId like so:

Aggregated Table

Then, ultimately, show only devices where the type3 count and type4 counts differ:

Different Counts Only

I know I will need to use COUNT and GROUP BY and possibly a nested SELECT?

I've tried:

SELECT 
    deviceId, 
    MIN(eventType) AS [Type3], 
    MAX(eventType) AS [Type4]
FROM 
    EventLog
WHERE 
    eventType IN (3,4)
GROUP BY 
    deviceId;

But that just returns the minimum and maximum value for each deviceID instead of counting the occurrences of type 3 and type 4 eventTypes.

I'm having trouble figuring out how to split the eventType values into their own columns.

I've also tried:

SELECT deviceId, COUNT(eventType)
FROM eventLog
WHERE eventType = 3
GROUP BY deviceId;

But that (obviously) only returns the count per deviceId for eventType 3 - and I need a column for eventType 3 AND one for eventType4.

CodePudding user response：

You can sum the cases in your select and filter in the having where those sums don't match.

SELECT 
    deviceId, 
    SUM(CASE WHEN eventType = 3 THEN 1 ELSE 0 END) as type3Count,
    SUM(CASE WHEN eventType = 4 THEN 1 ELSE 0 END) as type4Count
FROM 
    eventLog
WHERE 
    eventType IN(3,4)
GROUP BY 
    deviceId
HAVING
    SUM(CASE WHEN eventType = 4 THEN 1 ELSE 0 END) <> SUM(CASE WHEN eventType = 3 THEN 1 ELSE 0 END)