Use group by and with to produce special results

Raw data is as follows:

Date	Status
2012-01-01	Win
2012-01-01	Win
2012-01-01	Win
2012-01-01	Lose
2012-01-01	Lose
2012-01-02	Win
2012-01-02	Win
2012-01-02	Lose

 

The query is shown as follows:

Date	Win	Lose
2012-01-01	3	2
2012-01-02	2	1

 

View code

 1 With tbdate
 2 (
 3 Select date from tbusers group by date
 4 ),
 5 Tbwin
 6 (
 7 Select date, count (statu) as win from tbusers Where Statu = '  Win  ' Group by date
 8 ),
 9 Tblose
 10 (
 11 Select date, count (statu) as lose from tbusers where statu = '  Lose  ' Group by date
 12 )
 13 Select D. Date, T. Win, L. Lose from tbwin T, tblose L, tbdate d Where D. Date = T. Date and L. Date = D. Date

Another type:

View code

 1       With T As 
 2 (
3          Select Date, Count (Statu) As Win From Tbusers Where Statu =   '  Win  '   Group   By Date
 4 ),
 5 DAs (
 6       Select Date, Count (Statu) As Lose From Tbusers Where Statu =   '  Lose  '   Group   By Date
 7 )
8       Select T. Date, T. Win, D. Lose From T, d Where T. Date = D. Date