StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle SQL Query to Summarize Statistics, using GROUP BY
text
Body
copied!<p>I have an Oracle table with data that looks like this:</p> <pre><code>ID BATCH STATUS 1 1 0 2 1 0 3 1 1 4 2 0 </code></pre> <p>That is, <strong>ID</strong> is the primary key, there will be multiple rows for each "batch," and each row will have a status code in the <strong>STATUS</strong> column. There are a bunch of other columns, but these are the important ones.</p> <p>I need to write a query which will summarize the <strong>status codes</strong> for each <strong>batch</strong>; 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:</p> <pre><code>BATCH STATUS0 STATUS1 STATUS2 1 2 1 0 2 1 0 0 </code></pre> <p>Those numbers would be counts; for batch 1, there are </p> <ul> <li>2 records where <strong>STATUS</strong> is set to 0</li> <li>1 record where <strong>STATUS</strong> is set to 1, and</li> <li>no records where <strong>STATUS</strong> is set to 0. </li> </ul> <p>For batch 2, there is </p> <ul> <li>1 record where <strong>STATUS</strong> is set to 0, and</li> <li>no records where <strong>STATUS</strong> is set to 1 or 2.</li> </ul> <p>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:</p> <pre><code>SELECT batch, COUNT(status) FROM table WHERE status = 0 GROUP BY batch </code></pre> <p>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.</p> <p>If it makes a difference, aside from the <strong>STATUS</strong> column there is <em>another</em> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload