Count, sum and group by 1

[graabein]

Hi, I can't get my head around this problem:

I have one table which records name, weekday and type of operation. I want to do a select query that returns every name and how many records there are for each name, and also how many of those are weekend/weekday and the count of each type.

Let's say I have the following table:

Name | Date       | Type
A    | 2006-05-01 | 0
A    | 2006-04-30 | 1
A    | 2006-05-02 | 2

Then I want the following result:

Name | Total | Regular | Special | Weekend | Mon | Tue...
A    | 3     | 1       | 2       | 1       | 1   | 1

So I have to do select on Name and count(*) for Total. I also have to filter on Type (0 = regular, 1 and 2 = special) and Date (Saturday and Sunday is weekend, all others have their own column).

I get in trouble when I do case-clause on Date and Type because I do not have them in group-by or aggreagate function. I tried with case-and sum but no such luck.

Any help would be much appreciated!


graabein

 

[r937]

select Name
     , count(*) as Total
     , sum(case when Type=0
                then 1
                else 0 end) as Regular 
     , sum(case when Type=0
                then 0
                else 1 end) as Special
     , sum(case when [COLOR=red]weekday(Date)[/color] 
                  in (1,7)
                then 1
                else 0 end) as Weekend
     , sum(case when [COLOR=red]weekday(Date)[/color] 
                   = 2
                then 1
                else 0 end) as Mon 
     , ...

note that the weekday(Date) function will depend largely on whichever database you're using

r937.com | rudy.ca

 

[graabein]

Thanks! That's great! I didn't think to put the case inside of the sum function. Now I know.