SQL Query Count Trick

I have a logging table that just logs the piece of equipment and then what “action or event” happened to it with a timestamp. The problem is that I had to format the result set to a nice looking table.

Say I already have a query that i am using to filter down some records. I am just filtering bag events that happened to all the conveyors during the day. Here is an example of the results of that query

Device Description

Conveyor1 Bag Jam
Conveyor1 Bag Jam
Conveyor2 Bag Jam
Conveyor2 Missing Bag Jam
Conveyor2 Missing Bag Jam
Conveyor2 Bag Jam
Conveyor3 Missing Bag Jam
Conveyor3 Missing Bag Jam

Now by device, i want to list my device, then bag jams, then missing bag jams

Device Bag Jam Missing Bag Jam

Conveyor1 2 0
Conveyor2 2 2
Conveyor3 0 2

I tried and tried using where clauses and subqueries, but here is what i found that worked…

count(Case Description when ‘Bag Jam’ then Description end) as BagJam
count(Case Description when 'Missing Bag Jam then Description end) as MissingBagJam

) as mytable
GROUP BY device

The case effectivly works as a where condition except it doesnt strip down your dataset at the end.
This way you can re-query the same column of data with a different condition :wink:

Very nice. SQL is an extremely powerful language, but sometimes it makes your brain do some gymnastics to get the results you want.