CODEWITHSUNDEEP
mysqlsql
pranav nerurkar
pranav nerurkar

Reputation: 648

MySQL query for COUNT Distinct rows

I have a mysql table with two columns 'test-id' and 'q-id'. 'test-id' range is from 1 to 160 and each value of those test-id has upto 100 'q-id'.

ex: test-id   q-id
    1         126    #first row
    1         134    #second row
    ...
    1         66    #hundred row

SELECT COUNT(DISTINCT `q-id`)
             from `test`
             WHERE `test-id` = 1;  #answer is 100

I want to count which test-id has how many q-id. how to do it in a single query?

Upvotes: 0

Views: 39

Answers (2)

Andam
Andam

Reputation: 2177

SELECT testid, COUNT(DISTINCT q-id) from test Group by testid

Upvotes: 2

AmourK
AmourK

Reputation: 732

The key statement to understand is the GROUP BY statement.

SELECT "test-id", COUNT(DISTINCT "q-id") as qid_count FROM "test" GROUP BY "test-id"

Upvotes: 2

Related Questions