Group by SQL statement

Hi,
I have a table this.
Name diag1 diag2 diag3 diag4
john abc bcd abc xyz
david bcd abc xyz def
john efh slx def abc

I want to get a count of all abc's, bcd's, xyz, etc for john.
the result set I am expecting is :
john abc(2) bcd(3) xyz(2) etc...
david abc(1) bcd(2)..

Could somebody please provide a sql on how to acheive this..

Solution: Group by SQL statement

something like this?

                       
             select Name, diag, count(diag) from (
select Name, diag1 as diag from yourtable
union all
select Name, diag2 as diag from yourtable
union all
select Name, diag3 as diag from yourtable
union all
select Name, diag4 as diag from yourtable
) a
group by Name, diag

Toggle Highlighting

Open in New Window

Select All

Group by SQL statement

Solution: Group by SQL statement

Popular Tags