Group byArrrghhh!!!

Znowu się spotykamy mój odwieczny przeciwniku (nie włączając w to żółtego ekranu śmierci ASP.NET).

Człowiek programuje od wiele lat ,ale z jakiegoś powodu moje neurony wciąż myślą ,że polecenie GROUP BY działa tak jakby tego chciał. W rzeczywistość tak nie jest i widzę po raz kolejny ten sam błąd. Nic więc dziwnego ,że w całym SQL polecenie GROUP BY należy do jednych z najbardziej znienawidzonych przeze mnie funkcji.

Dzisiaj jednak nastał dzień sądu przyjacielu, ponieważ raz na zawsze utrwalę sobie jak działa funkcja GROUP BY.

O co chodzi SQL-owi

Każdego programista, który pracuje ze SQL musi to zrozumieć:

Za poleceniem GROUP BY stoi prosta logika. Złamanie tej logiki jest równoznaczne z dzieleniem przez zero w matematyce.

Powiedzmy ,że bym zadał pewnej osobie takie pytanie:

Z każdego miasta w Polsce jak jest suma ich mieszkańców oraz jaki wiek mają mieszkańcy.

Teraz pomyśl logicznie i dokładnie przeanalizuj te pytanie i potratuje je jako pojedyncze zapytanie. Czy ono ma sens? Suma mieszkańców ma sens, ponieważ jest to pojedyncza wartość. Czy jednak mogę zwrócić ich wiek? O co tak naprawdę pytam?

Proszę o listę wieku każdej osoby? Chyba nie bo nie ma to sensu w takim kontekście. Nic nie wspomniałem o sumie wieku czy o przeciętnej wieku ,a to by miałoby sens w tym zdaniu ,ale tak tego nie zapisałem.

Tak mniej więcej czuje się SQL. Delikatnie informuję cię stary ta kolumna nie jest w funkcji agregującej ,ani nie jest w poleceniu GROUP BY więc co ty robisz.

Oto przykład SQL obrazującym ten problem.

SQLCREATE TABLE #Citizen
(
	City VARCHAR(50),
	Age INT,
	COUNTRY VARCHAR(50),
)

INSERT INTO #Citizen VALUES
('Lublin',10,'Polska'),('Lublin',20,'Polska'),
('Warszawa',44,'Polska'),('Warszawa',28,'Polska'),
('Warszawa',17,'Polska'),('Warszawa',68,'Polska'),
('Berlin',27,'Niemcy'),('Berlin',38,'Niemcy')

SELECT City, COUNT(*) AS LiczbaMieszkańców
,Age --Wiek  
FROM #Citizen
WHERE Country = 'Polska'
GROUP BY  City

DROP TABLE #Citizen

Jak widać te zapytanie skompilowało się z błędem.

Msg 8120, Level 16, State 1, Line 14
Column '#Citizen.Age' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SQL nie może odpowiedź na pytanie z wiekiem.

To nie jest błąd składni czy jakiś dziwoląg implantacyjny SQL-a to jest dokładnie błąd logiczny samego zapytania.

Musimy więc zadać bardziej specyficzne i logiczne pytanie:

Z każdego miasta w Polsce jak jest suma ich mieszkańców oraz jaki jest średnia ich wieku.

SQLCREATE TABLE #Citizen
(
	City VARCHAR(50),
	Age INT,
	COUNTRY VARCHAR(50),
)

INSERT INTO #Citizen VALUES
('Lublin',10,'Polska'),('Lublin',20,'Polska'),
('Warszawa',44,'Polska'),('Warszawa',28,'Polska'),
('Warszawa',17,'Polska'),('Warszawa',68,'Polska'),
('Berlin',27,'Niemcy'),('Berlin',38,'Niemcy')

SELECT City, COUNT(*) AS LiczbaMieszkańców
,AVG(Age) --Wiek  
FROM #Citizen
WHERE Country = 'Polska'
GROUP BY  City

DROP TABLE #Citizen

Jeżeli wciąż nie ma to dla ciebie sensu proszę napisz komentarz do tego wpisu.

To nie koniec

Jak zapewne zauważyłeś na początku wpisu zaznaczyłem ,że moje neurony z jakiegoś powodu myślą ,że tak te zapytanie działają. Wynika to jednak z innego problemu, kiedy oprócz grupowania wartość z jednej tabelki chcemy także JOIN-ować drugą. A wtedy właśnie chcemy tą problematyczną kolumnę wiek wyświetlić ,jednak nie możemy, ponieważ nie znajduje się ona w funkcji agregującej.

Jest to klasyczny problem ,a ja z jakiegoś powodu zakładałem ,że SQL jest na tyle inteligent ,że sam się domyślni czego chce.

W następnym wpisie opiszę technikę GROUP BY związaną z JOIN-owaniem innych tabel.