Maria Maria - 1 year ago 78
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.course, t2.course
from data t1
JOIN data t2 ON
where t1.course<>t2.course)
group by,t1.course,t2.course)
order by total desc;

Answer Source

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

  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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download