CODEWITHSUNDEEP
mysqlcount
csc-mm
csc-mm

Reputation: 1

How to count unique value associated with a specific row in another column in MySQL?

I have a table like this, said it tableA. I need to count how many unique value of 'target' column for each unique 'source' column. How can I do this with select count?

Source  Target
10000   1026
10000   1027
10000   1147
10000   2308
10000   2475
10000   3551
10000   4193
10000   4792
10000   5139
10000   5925
10000   7157
10000   7249
10000   8517
1019    5925
1021    5925
10235   6237
1026    1019
1026    1021
1026    5925
1027    1019
1027    1021
1027    5925

So then the expected result will be:

UniqSource  No_UniqTarget
10000   13
1019    1
1021    1
10235   1
1026    3
1027    3

My (bad) solution is to get unique of column source and unique of column target but still not fullfill the expectation.

select count(distinct(Target)) from tableA
where Source='10000'

For this simple code I can find the result for each unique source, but how to make it automatically as in the expected result above?

Upvotes: 0

Views: 47

Answers (2)

Aakash Martand
Aakash Martand

Reputation: 944

Try this: 

SELECT Source AS UniqSource, COUNT(DISTINCT(Target)) AS No_UniqTarget 
  FROM tableA 
GROUP BY UniqSource;

COUNT(DISTINCT(Target)) also can be written as COUNT(DISTINCT Target)

Upvotes: 1

Ronak Chauhan
Ronak Chauhan

Reputation: 706

Here is the query

SELECT Source,count(*) as No_UniqTarget FROM tableA GROUP BY Source

Upvotes: 0

Related Questions