Spojení a skupiny

Spojení a GROUP BY se dobře doplňují: spojení dá data z několika tabulek do řádků a GROUP BY je shrne. Kolik kusů her kterého žánru si zákazníci objednali? Počty kusů jsou v tabulce polozky, žánry v tabulce hry:

SELECT h.zanr, SUM(p.pocet) AS kusy
FROM polozky AS p
JOIN hry AS h ON p.hra_id = h.id
GROUP BY h.zanr
ORDER BY kusy DESC, h.zanr;

Spojení dá řádek pro každou položku, s žánrem její hry. GROUP BY pak vytvoří skupinu pro každý žánr a SUM v každé skupině sečte počty kusů.

S penězi je to stejné. Položka vynesla počet kusů krát cenu, kterou zákazník zaplatil za jeden kus: p.pocet * p.cena. Ne h.cena: to je cena v katalogu, a ve slevě zákazníci platí méně. Peníze, které skupina vynesla, tedy její tržba, jsou pak SUM(p.pocet * p.cena): SUM spočítá výraz pro každý řádek skupiny a výsledky sečte.

Když seskupujete podle něčeho, co má jméno, třeba podle zákazníka nebo hry, seskupte podle jeho id a jméno přidejte do GROUP BY také: GROUP BY z.id, z.jmeno. Dva zákazníci se můžou jmenovat stejně a samotné GROUP BY z.jmeno by je dalo do jedné skupiny, jako by to byl jeden člověk. Díky jménu v GROUP BY ho můžete ukázat v SELECT: každá skupina má jedno id, a tedy i jedno jméno. Kolik recenzí napsal který zákazník a kolik hvězdiček v průměru dává?

SELECT z.jmeno, COUNT(*) AS pocet_recenzi, ROUND(AVG(r.hvezdicky), 1) AS prumer
FROM recenze AS r
JOIN zakaznici AS z ON r.zakaznik_id = z.id
GROUP BY z.id, z.jmeno
ORDER BY pocet_recenzi DESC, z.jmeno;

Vrácená objednávka nevynesla nic, takže tržba má počítat jen zaplacené objednávky. Stav je v tabulce objednavky: připojte i ji a zaplacené objednávky vyberte pomocí WHERE. WHERE se provádí před GROUP BY, takže řádky vrácených objednávek zmizí dřív, než se cokoli sčítá. HAVING pak vybírá skupiny, jak znáte z minulé lekce.

Typická chyba: SUM(pocet * cena) bez aliasů. Když je v dotazu tabulka hry, je cena nejednoznačná a dostanete chybu. SUM(p.pocet * h.cena) je ještě horší: proběhne, ale sečte ceny z katalogu a sleva z tržby zmizí.