'Proper' way of doing multiple GROUP BY
Problem
All items in the select clause must be in the group by clause, except for the aggregate functions, such as MIN,MAX,SUM,COUNT...
SELECT projno,projname,COUNT(projno)
FROM project
GROUP BY projno;
FROM project
GROUP BY projno;
You get the following error message:
Msg 8120, Level 16, State 1, Line 1 Column 'project.PROJNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So people stuff all the select statement into the group by, just to make it work! And it does, but does this make it right?
Data used & Alternative 1
USE adventureworks;
go
SELECT SC.CustomerID,
CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25)) AS 'Customer',
CAST(SC.CountryRegionName AS VARCHAR(18)) AS 'Region',
SC.City,
SC.StateProvinceName AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
FROM Sales.vIndividualCustomer SC
INNER JOIN Sales.SalesOrderHeader SH
ON SC.CustomerID = SH.CustomerID
WHERE SH.ShipDate >= '2004-03-01' and SH.ShipDate < '2004-03-02'
GROUP BY SC.CustomerID, SC.FirstName +', '+ SC.LastName,
SC.CountryRegionName, SC.City, SC.StateProvinceName;
go
go
SELECT SC.CustomerID,
CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25)) AS 'Customer',
CAST(SC.CountryRegionName AS VARCHAR(18)) AS 'Region',
SC.City,
SC.StateProvinceName AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
FROM Sales.vIndividualCustomer SC
INNER JOIN Sales.SalesOrderHeader SH
ON SC.CustomerID = SH.CustomerID
WHERE SH.ShipDate >= '2004-03-01' and SH.ShipDate < '2004-03-02'
GROUP BY SC.CustomerID, SC.FirstName +', '+ SC.LastName,
SC.CountryRegionName, SC.City, SC.StateProvinceName;
go
CustomerID Customer Region City State/Prov TotalSales ----------- ------------------------- ------------------ ------------------------------ -------------------------------------------------- --------------------- 11019 Luke, Lal Canada Langley British Columbia 2.29 11111 Meredith, Gutierrez Australia Geelong Victoria 2418.05 11122 Byron, Vazquez Australia North Sydney New South Wales 24.99 11185 Ashley, Henderson Canada Metchosin British Columbia 21.49 11242 Larry, Munoz France Versailles Yveline 2384.07 11269 Ryan, Foster United States Daly City California 2.29 11502 Jared, Peterson Canada Shawnee British Columbia 29.98 11553 Sharon, Luo United Kingdom Reading England 21.49 11990 Joanna, Vazquez Australia Newcastle New South Wales 2419.06 12730 Karla, Becker United Kingdom York England 55.91 12732 Todd, Guo France Orleans Loiret 14.98 12779 Mason, Rogers United States Yakima Washington 2401.96 13678 Warren, Goel United Kingdom High Wycombe England 2354.98 15056 Jaime, Suarez United Kingdom London England 751.34 15389 Joan, King United States Palo Alto California 2369.97 15510 Ernest, She United States Beaverton Oregon 39.98 15541 Zoe, Morris United States Palo Alto California 61.96 15678 Susan, Zhou France Morangis Essonne 2318.96 16038 Roy, Sai United Kingdom Leeds England 24.27 16598 Kate, Rai United Kingdom Bury England 2294.99 16602 Jill, Travers United Kingdom Runcorn England 69.99 16758 Justin, Perry Canada Victoria British Columbia 2319.99 16939 Bailey, Evans Germany Erlangen Bayern 29.98 16959 Carson, Foster Canada Royal Oak British Columbia 14.98 17075 Aimee, Guo Australia Coffs Harbour New South Wales 27.28 17181 Jenny, Lal Germany Stuttgart Saarland 2329.98 17246 Micah, Wu Australia Coffs Harbour New South Wales 801.77 17338 Jordyn, Long Canada Royal Oak British Columbia 14.98 17551 Victoria, Torres Canada Shawnee British Columbia 31.97 17958 Ricardo, Lal United Kingdom London England 629.96 18196 Ivan, Rana Australia Coffs Harbour New South Wales 554.97 18672 Anthony, Lewis United States Torrance California 128.97 18770 Aaron, Hernandez United States Burbank California 94.48 19042 Ronnie, Cai Germany Darmstadt Hessen 64.97 20289 Jada, Richardson United States Mill Valley California 69.99 20360 Brianna, Russell United States Imperial Beach California 78.98 21191 Willie, Lu Australia Rhodes New South Wales 1725.98 21526 Jonathon, Munoz Germany Offenbach Saarland 29.99 22601 Aimee, Gao France Orleans Loiret 565.47 23018 Richard, White United States Redmond Washington 607.96 23090 Christopher, Lee United States Lynnwood Washington 37.27 23296 Molly, Mehta United States Bellflower California 570.47 23788 Michael, Smith United States Burbank California 1214.85 23900 Rafael, Yang Germany Frankfurt Bayern 539.99 23995 Bradley, Carson Australia Melton Victoria 539.99 24370 Nathan, West Australia Cranbourne Victoria 67.93 25112 Suzanne, Zhu Australia Goulburn New South Wales 1155.48 25252 Warren, Zhang Australia Coffs Harbour New South Wales 1193.95 25614 Deanna, Subram France Paris Seine (Paris) 1228.83 25668 Carolyn, Lopez United Kingdom Bury England 88.97 25773 Jack, Simmons Germany Paderborn Nordrhein-Westfalen 60.47 25988 Victor, Muñoz United States Beaverton Oregon 64.97 26097 Robert, Flores United States San Francisco California 2398.05 26111 Sarah, Coleman United States Long Beach California 2419.06 26196 Gavin, Griffin United States Imperial Beach California 29.98 26491 Riley, Sanchez United States Bellingham Washington 2420.34 27179 Jamie, Rubio United Kingdom Billericay England 548.98 27529 Rachel, White United States San Carlos California 58.47 27754 Marcus, Gonzalez United States La Jolla California 23.78 28505 Barbara, Xie Germany Grevenbroich Bayern 827.33 28605 Caleb, Coleman United States Glendale California 7.95 (61 row(s) affected)
SELECT SC.CustomerID,
CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25)) AS 'Customer',
CAST(SC.CountryRegionName AS VARCHAR(18)) AS 'Region',
SC.City,
SC.StateProvinceName AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
...
GROUP BY SC.CustomerID, SC.FirstName +', '+ SC.LastName,
SC.CountryRegionName, SC.City, SC.StateProvinceName
...
CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25)) AS 'Customer',
CAST(SC.CountryRegionName AS VARCHAR(18)) AS 'Region',
SC.City,
SC.StateProvinceName AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
...
GROUP BY SC.CustomerID, SC.FirstName +', '+ SC.LastName,
SC.CountryRegionName, SC.City, SC.StateProvinceName
...
- The whole select has been stuffed in the group by except for the sales subtotal.
- What I wanted was just: to group by CustomerID, but because of the errors, I had to stuff them in the group by.
Alternative 2
SELECT SC.CustomerID,
MIN(CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25))) AS 'Customer',
MIN(CAST(SC.CountryRegionName AS VARCHAR(18))) AS 'Region',
MIN(SC.City),
MIN(SC.StateProvinceName) AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
FROM Sales.vIndividualCustomer SC
INNER JOIN Sales.SalesOrderHeader SH
ON SC.CustomerID = SH.CustomerID
WHERE SH.ShipDate >= '2004-03-01' and SH.ShipDate < '2004-03-02'
GROUP BY SC.CustomerID;
go
MIN(CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25))) AS 'Customer',
MIN(CAST(SC.CountryRegionName AS VARCHAR(18))) AS 'Region',
MIN(SC.City),
MIN(SC.StateProvinceName) AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
FROM Sales.vIndividualCustomer SC
INNER JOIN Sales.SalesOrderHeader SH
ON SC.CustomerID = SH.CustomerID
WHERE SH.ShipDate >= '2004-03-01' and SH.ShipDate < '2004-03-02'
GROUP BY SC.CustomerID;
go
CustomerID Customer Region State/Prov TotalSales ----------- ------------------------- ------------------ ------------------------------ -------------------------------------------------- --------------------- 11019 Luke, Lal Canada Langley British Columbia 2.29 11111 Meredith, Gutierrez Australia Geelong Victoria 2418.05 11122 Byron, Vazquez Australia North Sydney New South Wales 24.99 ... 27529 Rachel, White United States San Carlos California 58.47 27754 Marcus, Gonzalez United States La Jolla California 23.78 28505 Barbara, Xie Germany Grevenbroich Bayern 827.33 28605 Caleb, Coleman United States Glendale California 7.95 (61 row(s) affected)
- I have truncated the output, it is identical to the alternative 1.
- This is already a little bit better, because there is only 1 group by: CustomerID, which is what we want.
- But we now have to deal with a very messy select statement:
SELECT SC.CustomerID,
MIN(CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25))) AS 'Customer',
MIN(CAST(SC.CountryRegionName AS VARCHAR(18))) AS 'Region',
MIN(SC.City),
MIN(SC.StateProvinceName) AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
...
MIN(CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25))) AS 'Customer',
MIN(CAST(SC.CountryRegionName AS VARCHAR(18))) AS 'Region',
MIN(SC.City),
MIN(SC.StateProvinceName) AS 'State/Prov',
SUM(SH.Subtotal) AS TotalSales
...
- The only reason to have the MIN( is to avoid the error message:
...is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Proper solution
SELECT SC.CustomerID,
CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25)) AS 'Customer',
CAST(SC.CountryRegionName AS VARCHAR(18)) AS 'Region',
SC.City,
SC.StateProvinceName AS 'State/Prov',
SH.TotalSales
FROM Sales.vIndividualCustomer SC
INNER JOIN
(SELECT CustomerID, SUM(SubTotal) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE ShipDate >= '2004-03-01' and ShipDate < '2004-03-02'
GROUP BY CustomerID) SH
ON SC.CustomerID = SH.CustomerID;
go
CAST(SC.FirstName +', '+ SC.LastName AS VARCHAR(25)) AS 'Customer',
CAST(SC.CountryRegionName AS VARCHAR(18)) AS 'Region',
SC.City,
SC.StateProvinceName AS 'State/Prov',
SH.TotalSales
FROM Sales.vIndividualCustomer SC
INNER JOIN
(SELECT CustomerID, SUM(SubTotal) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE ShipDate >= '2004-03-01' and ShipDate < '2004-03-02'
GROUP BY CustomerID) SH
ON SC.CustomerID = SH.CustomerID;
go
CustomerID Customer Region State/Prov TotalSales ----------- ------------------------- ------------------ ------------------------------ -------------------------------------------------- --------------------- 11019 Luke, Lal Canada Langley British Columbia 2.29 11111 Meredith, Gutierrez Australia Geelong Victoria 2418.05 11122 Byron, Vazquez Australia North Sydney New South Wales 24.99 ... 27529 Rachel, White United States San Carlos California 58.47 27754 Marcus, Gonzalez United States La Jolla California 23.78 28505 Barbara, Xie Germany Grevenbroich Bayern 827.33 28605 Caleb, Coleman United States Glendale California 7.95 (61 row(s) affected)
- I have truncated the output, it is identical to the alternative 1 and alternative 2.
- The group by is done only on the CustomerID of the Sales.SalesOrderHeader.
- The group by SHOULD be done in the most inner subquery and on the primary key of that table.