Maria Maria - 1 month ago 14
SQL Question

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;

Answer

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