CODEWITHSUNDEEP
sqlsql-servercountpivotunpivot
rucquoy
rucquoy

Reputation: 5

SQL count distinct values appearing in different columns

i want to count distinct values and report their occurrences in SQL. The thing is i need to count occurrences on different columns and then report, for each column, the number of occurrences per value. So, say i have data like this from a table called Result :

Id OwnerId PilotId CoachId
A 1 2 3
B 2 2 3
C 3 1 3

Then my result should be :

Id Owner Pilot Coach
1 1 1 0
2 1 2 0
3 1 0 3

I tried using COUNT function with unions to get each result individually and it works. But i want to have the results on the same row per Id. Can anyone help me with this ?

Upvotes: 0

Views: 157

Answers (2)

PandaCheLion
PandaCheLion

Reputation: 476

The same using basic SQL commands. assured to work on every DBMS:

SELECT 
  ids.id, 
  SUM(CASE WHEN ro.ownerid = ids.id THEN 1 ELSE 0 END) AS Owner,
  SUM(CASE WHEN po.pilotid = ids.id THEN 1 ELSE 0 END) AS Pilot,
  SUM(CASE WHEN co.coachid = ids.id THEN 1 ELSE 0 END) AS Coach
FROM (
  SELECT ownerID AS id FROM result UNION
  SELECT pilotID FROM result UNION
  SELECT coachID FROM result  
) ids
  LEFT JOIN result ro ON ids.id = ro.ownerid
  LEFT JOIN result po ON ids.id = po.pilotid
  LEFT JOIN result co ON ids.id = co.coachid
GROUP BY ids.id

Here in SQLFiddler

Upvotes: 0

GMB
GMB

Reputation: 222492

I think you want to unpivot the columns to rows, then count the occurences of each value. You can do:

select v.id,
    sum(case when v.col = 'owner' then 1 else 0 end) owner,
    sum(case when v.col = 'pilot' then 1 else 0 end) pilot,
    sum(case when v.col = 'coach' then 1 else 0 end) coach
from mytable t
cross apply (values (t.ownerid, 'owner'), (t.pilotid, 'pilot'), (t.coachid, 'coach')) as v(id, col)
group by v.id

Demo on DB Fiddle:

id | owner | pilot | coach
-: | ----: | ----: | ----:
 1 |     1 |     1 |     0
 2 |     1 |     2 |     0
 3 |     1 |     0 |     3

Upvotes: 1

Related Questions