Mega Code Archive
Using the GROUP BY Clause
<![CDATA[
4>
5> CREATE TABLE Classification (
6> Classif_ID integer NOT NULL PRIMARY KEY,
7> Classification varchar(25))
8> GO
1>
2> INSERT into Classification VALUES( 1,"Pop")
3> INSERT into Classification VALUES( 2,"Country")
4> INSERT into Classification VALUES( 3,"Alternative")
5> INSERT into Classification VALUES( 4,"Metal")
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE CD (
4> CD_ID integer NOT NULL PRIMARY KEY,
5> CD_Title varchar(40),
6> Composer_ID integer NOT NULL,
7> Classif_ID integer NOT NULL,
8> SalesPrice money,
9> AverageCost money)
10> GO
1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99)
2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99)
3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99)
4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99)
5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99)
6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99)
7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99)
8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99)
9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99)
10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99)
11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99)
12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99)
13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99)
14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99)
15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99)
16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99)
17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99)
18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99)
19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99)
20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99)
21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99)
22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99)
23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99)
24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99)
25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99)
26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99)
27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99)
28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99)
29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99)
30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99)
31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99)
32> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT CD.Classif_ID,Classification.Classification,
3> Count(CD.CD_ID) "Total Offerings"
4> FROM CD,Classification
5> WHERE CD.Classif_ID = Classification.Classif_ID
6> GROUP BY CD.Classif_ID,Classification.Classification
7> GO
Classif_ID Classification Total Offerings
----------- ------------------------- ---------------
1 Pop 19
2 Country 2
3 Alternative 3
4 Metal 7
(4 rows affected)
1>
2>
3> drop table Classification;
4> drop table CD;
5> GO
]]>