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

Question

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.

OMG Ponies · Accepted Answer · 2011-01-18 22:35:19Z

49

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.

answered Jan 18, 2011 at 22:35

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dscl Over a year ago

Thanks that was perfect, group by was escaping me for some stupid reason!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related