CODEWITHSUNDEEP
mysqlsql
J. KWOK
J. KWOK

Reputation: 23

SQL COUNT among multiple columns

I want to know what is the SQL if I want data to count from the occurence of a number with respect to data in another table.

TABLE1

Day1         Day2
--------------------
1            2
2            3
1            3
3            1

TABLE2

ID       NAME
------------------
1        John
2        Mary
3        Tom

The result I want from SQL will be this

ID       NAME        OCCUR
1        John        3
2        Mary        2
3        Tom         3

How will the SQL look like for getting this result?

Upvotes: 2

Views: 66

Answers (4)

Ullas
Ullas

Reputation: 11556

Combine day1 and day2 column to one, then count.

Query

select t1.ID, t1.Name, count(t2.[day]) as occur
from table2 t1
left join 
(select Day1 as [day] from table1
union all 
select Day2 as [day] from table1) t2
on t1.ID = t2.[day]
group by t1.ID, t1.Name;

Upvotes: 0

JoseSilva
JoseSilva

Reputation: 517

In table 1, you must make a foreign key for the table 2. Probably the id of user. With that:

Select t2.id, t2.name, Sum(t1.day) 
from table1 t1
innerjoin table2 t2 
on t1.id = t2.id

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Select id and name from table2 and get the amount of occurrences in table1 in a subquery:

select 
  id, 
  name, 
  (
    select sum
    (
      case when t1.day1 = t2.id then 1 else 0 end +
      case when t1.day2 = t2.id then 1 else 0 end
    )
    from table1 t1
    where t2.id in (t1.day1, t1.day2)
  ) as occur
from table2 t2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271031

It seems you want to count how many rows in table1 have a matching value in either column. One easy way is:

select t2.*,
       (select count(*)
        from table1 t1
        where t2.id in (t1.day1, t1.day2)
       ) as occur
from table2 t2;

Upvotes: 2

Related Questions