SQL query trouble SQLite

Question

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

Yogesh Sharma · Accepted Answer · 2018-03-20 05:08:10Z

4

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

answered Mar 20, 2018 at 5:08

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gaurav · Accepted Answer · 2018-03-20 05:21:25Z

1

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

answered Mar 20, 2018 at 5:21

Gaurav

1,09910 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL query trouble SQLite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related