/* Advanced SQL Programming Fall 2002 */
/* Grouping and aggregate functions */
/* */
1> use pubs2
2> go
/* */
/* Find maximum advance and number of titles for each publisher.*/
/* */
1> select pub_id, count(*), max(advance)
2> from titles
3> group by pub_id
4> go
pub_id
------ ----------- ------------------------
0736 5 10,125.00
0877 7 15,000.00
1389 6 8,000.00
(3 rows affected)
/* */
/* Break down by type without grouping? */
/* */
1> select pub_id, type, count(*), max(advance)
2> from titles
3> group by pub_id
4> go
pub_id type
------ ------------ ----------- ------------------------
1389 business 6 8,000.00
1389 business 6 8,000.00
0736 business 5 10,125.00
1389 business 6 8,000.00
0877 mod_cook 7 15,000.00
0877 mod_cook 7 15,000.00
0877 UNDECIDED 7 15,000.00
1389 popular_comp 6 8,000.00
1389 popular_comp 6 8,000.00
1389 popular_comp 6 8,000.00
0877 psychology 7 15,000.00
0736 psychology 5 10,125.00
0736 psychology 5 10,125.00
0736 psychology 5 10,125.00
0736 psychology 5 10,125.00
0877 trad_cook 7 15,000.00
0877 trad_cook 7 15,000.00
0877 trad_cook 7 15,000.00
(18 rows affected)
/* */
/* Can we fix the problem using distinct? */
/* */
1> select distinct pub_id, type, count(*), max(advance)
2> from titles
3> group by pub_id
4> go
pub_id type
------ ------------ ----------- ------------------------
0877 UNDECIDED 7 15,000.00
0736 business 5 10,125.00
1389 business 6 8,000.00
0877 mod_cook 7 15,000.00
1389 popular_comp 6 8,000.00
0736 psychology 5 10,125.00
0877 psychology 7 15,000.00
0877 trad_cook 7 15,000.00
(8 rows affected)
/* */
/* Notice that the count cannot be right in results above - */
/* we know there are only 18 titles in the database. */
/* Must group at least by all columns not in the aggregate */
/* function calculation: */
/* */
1> select pub_id, type, count(*), max(advance)
2> from titles
3> group by pub_id, type
4> go
pub_id type
------ ------------ ----------- ------------------------
0736 business 1 10,125.00
0736 psychology 4 6,000.00
0877 UNDECIDED 1 NULL
0877 mod_cook 2 15,000.00
0877 psychology 1 7,000.00
0877 trad_cook 3 8,000.00
1389 business 3 5,000.00
1389 popular_comp 3 8,000.00
(8 rows affected)
/* */
/* Groups only work when groups actually exist across rows - */
/* if price added to previous grouping, only two titles */
/* in one of the publisher/type groups have the same price. */
/* */
1> select pub_id, type, price, count(*), max(advance)
2> from titles
3> group by pub_id, type, price
4> go
pub_id type price
------ ------------ ------------------------ -----------
------------------------
0736 business 2.99 1
10,125.00
0736 psychology 7.00 1
6,000.00
0736 psychology 7.99 1
4,000.00
0736 psychology 10.95 1
2,275.00
0736 psychology 19.99 1
2,000.00
0877 UNDECIDED NULL 1
NULL
0877 mod_cook 2.99 1
15,000.00
0877 mod_cook 19.99 1
0.00
0877 psychology 21.59 1
7,000.00
0877 trad_cook 11.95 1
4,000.00
0877 trad_cook 14.99 1
8,000.00
0877 trad_cook 20.95 1
7,000.00
1389 business 11.95 1
5,000.00
1389 business 19.99 2
5,000.00
1389 popular_comp NULL 1
NULL
1389 popular_comp 20.00 1
8,000.00
1389 popular_comp 22.95 1
7,000.00
(17 rows affected)
/* */
/* Examples to show difference between where and having */
/* Find count and advance for titles with price > $10. */
/* */
/* Use where to narrow down selection before grouping. */
/* */
1> select pub_id, type, count(*), max(advance)
2> from titles
3> where price > 10
4> group by pub_id, type
5> go
pub_id type
------ ------------ ----------- ------------------------
0736 psychology 2 2,275.00
0877 mod_cook 1 0.00
0877 psychology 1 7,000.00
0877 trad_cook 3 8,000.00
1389 business 3 5,000.00
1389 popular_comp 2 8,000.00
(6 rows affected)
/* */
/* Use having (with distinct if necessary) to set condition */
/* after grouping and calculating. */
/* */
1> select pub_id, type, count(*), max(advance)
2> from titles
3> group by pub_id, type
4> having price > 10
5> go
pub_id type
------ ------------ ----------- ------------------------
1389 business 3 5,000.00
1389 business 3 5,000.00
1389 business 3 5,000.00
0877 mod_cook 2 15,000.00
1389 popular_comp 3 8,000.00
1389 popular_comp 3 8,000.00
0877 psychology 1 7,000.00
0736 psychology 4 6,000.00
0736 psychology 4 6,000.00
0877 trad_cook 3 8,000.00
0877 trad_cook 3 8,000.00
0877 trad_cook 3 8,000.00
(12 rows affected)
1> select distinct pub_id, type, count(*), max(advance)
2> from titles
3> group by pub_id, type
4> having price > 10
5> go
pub_id type
------ ------------ ----------- ------------------------
1389 business 3 5,000.00
0877 mod_cook 2 15,000.00
1389 popular_comp 3 8,000.00
0877 psychology 1 7,000.00
0736 psychology 4 6,000.00
0877 trad_cook 3 8,000.00
(6 rows affected)
/* */
/* Example using datetime as basis for grouping - in what */
/* month are most titles published? */
/* Look at data first: */
/* */
1> select title_id, pubdate from titles
2> go
title_id pubdate
-------- --------------------------
BU1032 Jun 12 1986 12:00AM
BU1111 Jun 9 1988 12:00AM
BU2075 Jun 30 1985 12:00AM
BU7832 Jun 22 1987 12:00AM
MC2222 Jun 9 1989 12:00AM
MC3021 Jun 18 1985 12:00AM
MC3026 Sep 15 1999 2:26PM
PC1035 Jun 30 1986 12:00AM
PC8888 Jun 12 1987 12:00AM
PC9999 Sep 15 1999 2:26PM
PS1372 Oct 21 1990 12:00AM
PS2091 Jun 15 1989 12:00AM
PS2106 Oct 5 1990 12:00AM
PS3333 Jun 12 1988 12:00AM
PS7777 Jun 12 1988 12:00AM
TC3218 Oct 21 1990 12:00AM
TC4203 Jun 12 1985 12:00AM
TC7777 Jun 12 1987 12:00AM
(18 rows affected)
1> select datepart(mm, pubdate) Month, count(*) NumPublished
2> from titles
3> group by datepart(mm, pubdate)
4> go
Month NumPublished
----------- -----------
6 13
9 2
10 3
(3 rows affected)
/* */
/* Note: Cannot group by alias */
/* */
1> select datepart(mm, pubdate) Month, count(*)
2> from titles
3> group by Month
4> go
Msg 207, Level 16, State 2:
Line 1:
Invalid column name 'Month'.
/* */
/* All aggregate functions except count remove nulls. */
/* Example: Find average advance two different ways. */
/* */
1> select advance from titles
2> go
advance
------------------------
5,000.00
5,000.00
10,125.00
5,000.00
0.00
15,000.00
NULL
7,000.00
8,000.00
NULL
7,000.00
2,275.00
6,000.00
2,000.00
4,000.00
7,000.00
4,000.00
8,000.00
(18 rows affected)
/* */
/* Avg(advance) averages 16 non-null values (above) */
/* */
1> select avg(advance) from titles
2> go
------------------------
5,962.50
(1 row affected)
/* */
/* Sum/count adds 16 non-null values, divides by all 18. */
/* */
1> select sum(advance)/count(*) from titles
2> go
------------------------
5,300.00
(1 row affected)
1> quit