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…
SELECT
device,
count(Case Description when ‘Bag Jam’ then Description end) as BagJam
count(Case Description when 'Missing Bag Jam then Description end) as MissingBagJam
FROM
(
) 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 