MySQL query for COUNT Distinct rows

Question

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?

Hassan Imam · Accepted Answer · 2018-01-20 05:31:33Z

2

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"

edited Jan 20, 2018 at 5:31

Hassan Imam

22.6k6 gold badges45 silver badges53 bronze badges

answered Jan 20, 2018 at 5:28

AmourK

7321 gold badge8 silver badges20 bronze badges

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

Comments

Hassan Imam · Accepted Answer · 2018-01-20 05:31:56Z

2

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

edited Jan 20, 2018 at 5:31

Hassan Imam

22.6k6 gold badges45 silver badges53 bronze badges

answered Jan 20, 2018 at 5:29

Andam

2,1971 gold badge10 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL query for COUNT Distinct rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related