Oracle SQL Query to Summarize Statistics, using GROUP BY
group-by, oracle, pivot, sql
Solution
select batch
, count(case when status=1 then 1 end) status1
, count(case when status=2 then 1 end) status2
, count(case when status=3 then 1 end) status3
from table
group by batch;
This is often called a "pivot" query, and I have written an article about how to generate these queries dynamically on my blog.
Version using DECODE (Oracle-specific but less verbose):
select batch
, count(decode(status,1,1)) status1
, count(decode(status,2,1)) status2
, count(decode(status,3,1)) status3
from table
group by batch;
Problem
I have an Oracle table with data that looks like this: ``` ID BATCH STATUS 1 1 0 2 1 0 3 1 1 4 2 0 ``` That is, ID is the primary key, there will be multiple rows for each "batch," and each row will have a status code in the STATUS column. There are a bunch of other columns, but these are the important ones. I need to write a query which will summarize the status codes for each batch; there are three possible values that can go in the STATUS column, 0, 1, and 2, and I would like output that looks something like this: ``` BATCH STATUS0 STATUS1 STATUS2 1 2 1 0 2 1 0 0 ``` Those numbers would be counts; for batch 1, there are - 2 records where STATUS is set to 0 - 1 record where STATUS is set to 1, and - no records where STATUS is set to 0. For batch 2, there is - 1 record where STATUS is set to 0, and - no records where STATUS is set to 1 or 2. Is there a way that I can do this in one query, without having to rewrite the query for each status code? i.e. I can easily write a query like this, and run it three times: ``` SELECT batch, COUNT(status) FROM table WHERE status = 0 GROUP BY batch ``` I could run that, then run it again where status = 1, and again where status = 2, but I'm hoping to do it in one query. If it makes a difference, aside from the STATUS column there is another column that I might want to summarize the same way--another reason that I don't want to have to execute SELECT statement after SELECT statement and amalgamate all of the results.