CODEWITHSUNDEEP
mysqlsqlselect
user3307783
user3307783

Reputation: 157

Count from columns

I have select SELECT COUNT(*),names,names2,whole FROM results GROUP BY names";

This select is working = how many names are repeat, but if i add same names to collect from names2 (GROPU BY names,names2) then doesnt work. Can someone show me how to implement it? I need names and names2 column to count and give me the numbers how many names are there. Thanks

EDIT

names names2
John  Michael
Peter John
Michael Peter
Peter Michael

I need to find all Micahel in names and names2 and give me a number of it.

Result: 

Michael 3
John 2
Peter 2

Upvotes: 1

Views: 60

Answers (1)

PerlDuck
PerlDuck

Reputation: 5720

I would use two SELECTS for name1 and name2 so that they appear as one column (name2 below name1, not side-by-side), then UNION ALL them and then do the count:

select x.name, count(*) from (
    select names as `name` from results
    UNION ALL
    select names2 as `name`from results) x
group by x.name;

The important thing is the ALL in UNION ALL because without ALL it would eliminate duplicates and that exactly what you don't want.

Upvotes: 3

Related Questions