CODEWITHSUNDEEP
sqlgroup-bydistinct
Drake .C
Drake .C

Reputation: 332

How to find distinct value (not count of distinct value) using sql

I got a table of some test data. Which is mainly made up of "Test criteria" and "serial number". A "serial number" stands for a particular test and a test has several Test criteria. A test could run several times due to failures so let's say there are 15 distinct test criteria to run but finally, if I do 'count(Test criteria) group by "serial number"', I could get more than 15.

What I am trying to do is to find the number for each Test criteria that ran.

For example, if we have test A and Test B. Test A has criteria 1, 2 to run and test B has 1,3 to run. Maybe criteria 1 will be tested for 6 times due to some reason. And 2, 3 are only tested once.

Finally, I wish I could get a table like this: 

Test criteria run time
   1           2
   2           1
   3           1

I tried to use count(distinct "") group by. However,it could only gives me the number but I want the value.

Any thoughts?

I really appreciate your help.

(here is some sample data)

Click here to see the original data

here is the table I want

(here is the table I want)

Upvotes: 1

Views: 60

Answers (1)

Golden Ratio
Golden Ratio

Reputation: 349

You could group by both the serial number and the test criteria. Then you can display the test criteria for each count.

select
    serialnumber,
    test_criteria,
    count(test_criteria) as RunTime
from table
group by
    serialnumber,
    test_criteria

If you only want the number of times each test was run and the test name, you can remove serialnumber from the group by.

select
    test_criteria,
    count(test_criteria) as RunTime
from table
group by
    test_criteria

Upvotes: 1

Related Questions