CODEWITHSUNDEEP
sqlcountgoogle-bigquery
Maria
Maria

Reputation: 13

counting appearence of difference combinations coming from same column?

I want to count how many person did participate in 2 course combinations

Let's say I have a table1:

Name    course
-----------------
Mary    Biology
Mary    Chemistry
Mary    Music
Kim     Music
Kim     Chemistry
Kim     Mathematics
Ida     Mathematics
Ida     Biology
Ida     Music

results should be like this

Biology   Chemistry   1
Biology   music       2
Chemistry music       2
Mathematics music     2

This is what I got, but isn't working.

select * From (
select t1.course, t2.course, count (*) AS total from 
(select
t1.name t1.course, t2.course
from data t1
JOIN data t2 ON t1.name=t2.name
where t1.course<>t2.course)
group by t1.name,t1.course,t2.course)
order by total desc;

Upvotes: 0

Views: 46

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

for BigQuery Legacy SQL or BigQuery Standard SQL (see Enabling Standard SQL)

SELECT 
  a.course as course_a, 
  b.course as course_b, 
  COUNT(*) as cnt
FROM rekry_data a 
JOIN rekry_data b 
ON a.Name=b.Name
WHERE a.course < b.course
GROUP BY a.course, b.course

Upvotes: 1

Behnam
Behnam

Reputation: 1063

SELECT a.course, b.course, COUNT(*)
FROM mytable a JOIN mytable b ON a.Name=b.Name AND a.Course <> b.Course 
GROUP BY a.course, b.course

Upvotes: 0

Related Questions