azmi27 azmi27 - 8 months ago 36
SQL Question

count and group data from 2 column on single table

i have a problem when counting and grouping data from 2 column on single table.

My table structure:
id, price, user_1, user_2

Data sample:

001 500 bergkamp cech<br>
002 100 cech ljungberg<br>
003 200 viera henry<br>
004 300 bergkamp pires<br>
005 200 lauren bergkamp<br>

My query:

count(user_1) as total1,
count(user_2) as total2
group by user_1 and user_2

results with not what i want,

I want the output like this:

bergkamp 3<br>
henry 1<br>
cech 2<br>
ljungberg 1<br>
lauren 1<br>
pires 1<br>
viera 1<br>

Any help will be so appreciated, thanks


Put both user columns into one with a UNION. Then group by that temp table result and count the names

select user_name, count(*)
    SELECT user_1 as user_name FROM sales
    union all 
    SELECT user_2 FROM sales
) tmp
group by user_name