Is this possible in sql - grouping and totalling.

Factual Questions
1

I have an sql query that gets bet totals grouped by track, bet type and month. Pseudo sample below…



July     | Joe's racetrack | Exacta       | $450
August   | Joe's racetrack | Exacta       | $320
September| Joe's racetrack | Exacta       | $930
July     | Joe's racetrack | Trifecta      | $123
August   | Joe's racetrack | Trifecta      | $231
September| Joe's racetrack | Trifecta      | $635
July     | Joe's racetrack | Superfecta | $123
August   | Joe's racetrack | Superfecta | $231
September| Joe's racetrack | Superfecta | $635

Would it be possible to create a result like this using pure SQL?..

July | Joe’s racetrack | Exacta | $450
August | Joe’s racetrack | Exacta | $320
September| Joe’s racetrack | Exacta | $930
| Joe’s racetrack | Exacta Total | $1700
July | Joe’s racetrack | Trifecta | $123
August | Joe’s racetrack | Trifecta | $231
September| Joe’s racetrack | Trifecta | $635
| Joe’s racetrack | Trifecta Total | $989
July | Joe’s racetrack | Superfecta | $111
August | Joe’s racetrack | Superfecta | $222
September| Joe’s racetrack | Superfecta | $333
| Joe’s racetrack | Superfecta Total | $666

2

Absolutely it’s possible, you will want to look at the WITH ROLLUP and (I think) COALESCE arguments for group by.

3

Um… I ran out of edit time to line up all the columns.

Thanks. Will do some googling. :slight_smile:

4

For your interest, here’s what I ended up with (with sensitive information masked)…

select case month(dbdate)
when 1 then ‘January’
when 2 then ‘February’
when 3 then ‘March’
when 4 then ‘April’
when 5 then ‘May’
when 6 then ‘June’
when 7 then ‘July’
when 8 then ‘August’
when 9 then ‘Septempber’
when 10 then ‘October’
when 11 then ‘November’
when 12 then ‘December’ else ‘’ end as month,
case when (grouping(pool_type)=1) then (select name from amtotetracks where our_code = program_name) + ’ Total’
else (select name from amtotetracks where our_code = program_name) end as program_name,
case when (grouping(month(dbdate))=1) then
(case pool_type
when ‘WN’ then ‘Win’
when ‘PL’ then ‘Place’
when ‘SH’ then ‘Show’
when ‘WP’ then ‘Win Place’
when ‘WS’ then ‘Win Show’
when ‘PS’ then ‘Place Show’
when ‘WPS’ then ‘Win Place Show’
when ‘EX’ then ‘Exacta’
when ‘QN’ then ‘Quinella’
when ‘TR’ then ‘Trifecta’
when ‘SF’ then ‘Superfecta’
when ‘E5’ then ‘Exact 5’
when ‘DB’ then ‘Daily Double’
when ‘P3’ then ‘Pick 3’
when ‘P4’ then ‘Pick 4’
when ‘P5’ then ‘Pick 5’
when ‘P6’ then ‘Pick 6’
when ‘P7’ then ‘Pick 7’
when ‘P8’ then ‘Pick 8’
when ‘P9’ then ‘Pick 9’
when ‘P10’ then ‘Pick 10’ else ‘’ end) + ’ Total’ else
(case pool_type
when ‘WN’ then ‘Win’
when ‘PL’ then ‘Place’
when ‘SH’ then ‘Show’
when ‘WP’ then ‘Win Place’
when ‘WS’ then ‘Win Show’
when ‘PS’ then ‘Place Show’
when ‘WPS’ then ‘Win Place Show’
when ‘EX’ then ‘Exacta’
when ‘QN’ then ‘Quinella’
when ‘TR’ then ‘Trifecta’
when ‘SF’ then ‘Superfecta’
when ‘E5’ then ‘Exact 5’
when ‘DB’ then ‘Daily Double’
when ‘P3’ then ‘Pick 3’
when ‘P4’ then ‘Pick 4’
when ‘P5’ then ‘Pick 5’
when ‘P6’ then ‘Pick 6’
when ‘P7’ then ‘Pick 7’
when ‘P8’ then ‘Pick 8’
when ‘P9’ then ‘Pick 9’
when ‘P10’ then ‘Pick 10’ else ‘’ end) end as pooltype,
sum(handle) as handle,sum(cast(credit_amount as money)) as winnings from amtoteaccountactivity where accountnumber in (‘XXXXXXX’,‘XXXXXXX’)
and _date between ‘09/01/01’ and ‘09/12/31’
and transaction_type = ‘Bet’
group by program_name,pool_type,month(dbdate)
with rollup