Extracting partial records using GROUP BY. : GROUP BY « Query « SQL Server / T-SQL Tutorial






4>
5>
6> CREATE TABLE Classification (
7>      Classif_ID         integer  NOT NULL PRIMARY KEY,
8>      Classification    varchar(25))
9> 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>
3> SELECT CD.Classif_ID,Classification.Classification,
4>          Count(CD.CD_ID) "Total Offerings"
5> FROM CD,Classification
6> WHERE CD.Classif_ID = Classification.Classif_ID and CD.SalesPrice >= 12.00
7> GROUP BY CD.Classif_ID,Classification.Classification
8> GO
Classif_ID   Classification            Total Offerings
----------- ------------------------- ---------------
          1 Pop                                    14
          2 Country                                 2
          3 Alternative                             3
          4 Metal                                   6

(4 rows affected)
1>
2> drop table Classification;
3> drop table CD;
4> GO








1.4.GROUP BY
1.4.1.A summary query that counts the number of Billings by Banker
1.4.2.Group by and table join
1.4.3.A summary query with a search condition in the WHERE clause
1.4.4.Extracting partial records using GROUP BY.
1.4.5.GROUP BY with SUM()
1.4.6.both the EmployeeID and CustomerID columns in our GROUP BY
1.4.7.A count for each grouping rather than the full table
1.4.8.Using GROUP BY ALL