CODEWITHSUNDEEP
sqloracle11g
Brian Wilson
Brian Wilson

Reputation: 135

distinct values in sql?

I need an sql query to count the number of distinct occurrences of the numbers 2, 5, 7, & 9 in the left hand column (table below).

I have played w DISTINCT and COUNT. Need a query to show how many of each value there are.

I know there's a bone-head simple solution to this, just cant figure it out. Thanks!

  CLASS_ID STUDENT_ID
---------- ----------
         2         12
         2         11
         2          2
         2          7
         2          8
         2         16
         2         21
         7         10
         7          2
         7          3
         7          4
         7          5
         7          6
         7          7
         7          8
         7          9
         9          2
         9         11
         9         12
         9         13
         9         14
         9         15
         9         16
         9         17
         9         18
         9         20
         9         21
         9         22
        10         12
        10          4
        10          3
        10          6
        10          8
        10          9
        10         12
        10         16
        10         18
        10         20
        10          2
        10          4

Upvotes: 0

Views: 67

Answers (3)

laz4
laz4

Reputation: 619

I think you should use 'group by':

select class_id, count(*) from tablename group by class_id

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460048

Use Group By, Where and Count:

SELECT CLASS_ID, COUNT(*) AS COUNT
FROM Table1
WHERE CLASS_ID IN (2, 5, 7, 9)
GROUP BY CLASS_ID

Demo

Upvotes: 3

N West
N West

Reputation: 6819

You want to add a GROUP BY clause, which groups your data for an aggregate (like count), to your SQL statement.

SELECT CLASS_ID, count(*) FROM TABLE GROUP BY CLASS_ID

If you only want to find classes with more than X students you can add a HAVING clause as well:

SELECT CLASS_ID, count(*) FROM TABLE GROUP BY CLASS_ID HAVING count(*) > 20

Upvotes: 2

Related Questions