CODEWITHSUNDEEP
sqlsqlite
calcMan
calcMan

Reputation: 111

SQL query trouble SQLite

4 colums

| day of year | year | user A | user B |

      1          1      john      ron
      2          1       ron      john
      1          2       john     kyle

i need to count how many times a user appears in either UserA colum or UserB colum

group by UserA colum is not succifient i need to count how many times john appears in total from UserA and UserB cols

expected output

john 3
ron 2
kyle 1

Upvotes: 1

Views: 45

Answers (2)

Gaurav
Gaurav

Reputation: 1109

You can write a query like :-

select user, sum(cnt) total
from 
(select userA as user, count(*) cnt from table group by userA
UNION ALL
select userB as user, count(*) cnt from table group by userB
) a11
group by user

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You appears to want union all

select user, count(*) counts from
(
    select [user A] as user from table t
    union all
    select [user B] as user from table t
)a
group by user

Upvotes: 4

Related Questions