GROUP BY 節

GROUP BY 節ã?¯é?¸æŠžå¼?ã?®ä¸€éƒ¨ã?¨ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?“ã?®ç¯€ã?«ã‚ˆã‚Šçµ?果を一列以上ã?®åˆ—ã?®é–“ã?§å?Œã?˜å€¤ã‚’æŒ?ã?¤ã€?çµ?æžœã?®éƒ¨åˆ†é›†å?ˆã?«ã?¾ã?¨ã‚?ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ ã??れã?žã‚Œã?®éƒ¨åˆ†é›†å?ˆã?«ã?¯ã€?分類列ã?Œå?Œã?˜å€¤ã‚’æŒ?ã?¤è¡Œã?¯ã?‚りã?¾ã?›ã‚“。 NULLã?¯éƒ¨åˆ†é›†å?ˆã?¸ã?®ã?¾ã?¨ã‚?を行ã?†å ´å?ˆã€?å?Œã?˜å€¤ã?¨è¦‹ã?ªã?•れã?¾ã?™ã€‚

典型的�����GROUP BY節�集約��併��使�れ��。

æ§‹æ–‡

GROUP BY 列å?? [ , 列å?? ] *

列å?? ã?¯å•?ã?„å?ˆã‚?ã?›ã?«ã?¦å?¯è¦–ã?§ã?ªã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。å•?ã?„å?ˆã‚?ã?›ã?«ã?¦ä¸?å?¯è¦–ã?ªåˆ—ã?§ã?‚ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。 例ã?ˆã?°ã€?GROUP BY節ã?Œå‰¯å•?ã?„å?ˆã‚?ã?›ã?«ã?‚ã?£ã?Ÿå ´å?ˆã€?ã??ã?®ç¯€ã?¯å‰¯å•?ã?„å?ˆã‚?ã?›ã?®å¤–ã?«ã?‚ã‚‹å•?ã?„å?ˆã‚?ã?›ã?®åˆ—ã‚’å?‚ç…§ã?—ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。

GROUP BY節を���択���る�択項目�集約�分類列���れ��り��ん。

-- airport�分類�れ�flights��
-- flying_times�平�を探�。
SELECT AVG (flying_time), orig_airport
FROM Flights
GROUP BY orig_airport

SELECT MAX(city_name), region
FROM Cities, Countries
WHERE Cities.country_ISO_code = Countries.country_ISO_code
GROUP BY region
-- smallint�よる分類
SELECT ID, AVG(SALARY)
FROM SAMP.STAFF
GROUP BY ID
-- AVGSALARY�EMPCOUNT列��DEPTNO列をAS節�より�得�る。
-- ã?•らã?«OTHERSã?¨ã?„ã?†ç›¸é–¢å??ã?§ã€?WORKDEPT列ã?«ã‚ˆã‚‹åˆ†é¡žã‚’行ã?†ã€‚
SELECT OTHERS.WORKDEPT AS DEPTNO,
AVG(OTHERS.SALARY) AS AVGSALARY,
COUNT(*) AS EMPCOUNT
FROM SAMP.EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT