# Is this possible in sql - grouping and totalling.
**URL:** https://boards.straightdope.com/t/is-this-possible-in-sql-grouping-and-totalling/528747
**Category:** Factual Questions
**Created:** [February 12, 2010, 6:34pm UTC](https://boards.straightdope.com/t/is-this-possible-in-sql-grouping-and-totalling/528747 "2010-02-12T18:34:02Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [February 12, 2010, 6:34pm UTC](https://boards.straightdope.com/t/is-this-possible-in-sql-grouping-and-totalling/528747/1 "2010-02-12T18:34:02Z")
I have an sql query that gets bet totals grouped by track, bet type and month. Pseudo sample below…
```auto
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
---
### Author:  [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)
#### Post date: [February 12, 2010, 6:39pm UTC](https://boards.straightdope.com/t/is-this-possible-in-sql-grouping-and-totalling/528747/2 "2010-02-12T18:39:08Z")
Absolutely it’s possible, you will want to look at the WITH ROLLUP and (I think) COALESCE arguments for group by.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [February 12, 2010, 6:40pm UTC](https://boards.straightdope.com/t/is-this-possible-in-sql-grouping-and-totalling/528747/3 "2010-02-12T18:40:16Z")
Um.. I ran out of edit time to line up all the columns.
> [@crazyjoe](#):
>
> Absolutely it’s possible, you will want to look at the WITH ROLLUP and (I think) COALESCE arguments for group by.
Thanks. Will do some googling. 🙂
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [February 12, 2010, 8:49pm UTC](https://boards.straightdope.com/t/is-this-possible-in-sql-grouping-and-totalling/528747/4 "2010-02-12T20:49:58Z")
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_