Getting Total On Groups of Data

[Michael42]

Hello,

I have a table with a LAB field and a CODE field. For each LAB field, how can I display the total CODES for each code type?

Example Table Data

lab   code
1     a
1     a
1     c
2     b
2     c
2     c
3     a
3     b
3     c

Example Desired Output:

LAB    CodeA   CodeB   CodeC
1      2       0       1
2      0       1       2
3      1       1       1

Thanks,

Michael42

 

[PHV]

Search your RDBMS documentation for CrossTab query or PivotTable.
Otherwise, another approch:
SELECT LAB, Code, Count(*) AS CountOfCode
FROM yourTable
GROUP BY LAB, Code

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

select lab
     , sum(case when code = 'a' 
                then 1 else 0 end) as codeA
     , sum(case when code = 'b' 
                then 1 else 0 end) as codeB
     , sum(case when code = 'c' 
                then 1 else 0 end) as codeC
  from yourtable
group
    by lab

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)