Oracle / SQL - Count number of occurrences of values in a single column

sql oracle group-by aggregate-functions
dscl picture dscl · Jan 18, 2011 · Viewed 49k times · Source

Okay, I probably could have come up with a better title, but wasn't sure how to word it so let me explain.

Say I have a table with the column 'CODE'. Each record in my table will have either 'A', 'B', or 'C' as it's value in the 'CODE' column. What I would like is to get a count of how many 'A's, 'B's, and 'C's I have.

I know I could accomplish this with 3 different queries, but I'm wondering if there is a way to do it with just 1.

Answer

OMG Ponies picture OMG Ponies · Jan 18, 2011

Use:

  SELECT t.code,
         COUNT(*) AS numInstances
    FROM YOUR_TABLE t
GROUP BY t.code

The output will resemble:

code   numInstances
--------------------
A      3
B      5
C      1

If a code exists that has not been used, it will not show up. You'd need to LEFT JOIN to the table containing the list of codes in order to see those that don't have any references.

Related questions

GROUP BY without aggregate function

I am trying to understand GROUP BY (new to oracle dbms) without aggregate function. How does it operate? Here is what i have tried. EMP table on which i will run my SQL. SELECT ename , sal FROM emp GROUP BY …

Read More
GROUP BY with MAX(DATE)

I'm trying to list the latest destination (MAX departure time) for each train in a table, for example: Train Dest Time 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK 13:00 2 SH 09:00 2 SZ 07:00 The desired result should be: Train Dest Time 1 SZ 14:00 2 HK 13:00 I have tried …

Read More
SQL not a single-group group function

When I run the following SQL statement: SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN It returns the maximum sum value of downloads by a customer, however if I try to find the social security number that that max …

Read More