Home > Archive > SQL_archive > ๐Ÿ’‰[SQL] GROUP BY

๐Ÿ’‰[SQL] GROUP BY
SQL

GROUP BY

โ€˜GROUP BYโ€™ ์ ˆ์€ SQL์—์„œ ํŠน์ • ์—ด(๋“ค)์˜ ๊ฐ’์— ๊ธฐ๋ฐ˜ํ•˜์—ฌ ํ–‰(row)๋“ค์„ ๊ทธ๋ฃนํ™”ํ•  ๋•Œ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

  • ์ด ๊ธฐ๋Šฅ์€ ์ง‘๊ณ„ํ•จ์ˆ˜(โ€˜SUMโ€™, โ€˜AVGโ€™, โ€˜COUNTโ€™, โ€˜MINโ€™, โ€˜MAXโ€™ ๋“ฑ)์™€ ํ•จ๊ผ ์‚ฌ์šฉ๋˜์–ด, ๊ฐ ๊ทธ๋ฃน์— ๋Œ€ํ•œ ์ง‘๊ณ„๋œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ณ„์‚ฐํ•˜๊ณ  ๋ฐ˜ํ™˜ํ•˜๋Š” ๋ฐ ์ฃผ๋กœ ํ™œ์šฉ๋ฉ๋‹ˆ๋‹ค.
  • โ€˜GROUP BYโ€™ ๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์š”์•ฝํ•˜๊ณ , ํŠน์ • ๊ธฐ์ค€์— ๋”ฐ๋ฅธ ๋ฐ์ดํ„ฐ์˜ ํ†ต๊ณ„๋ฅผ ๋ถ„์„ํ•  ๋•Œ ์œ ์šฉํ•˜๊ฒŒ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

โ€˜GROUP BYโ€™ ์‚ฌ์šฉ ์˜ˆ

  • ๊ทธ๋ฃน๋ณ„ ํ•ฉ๊ณ„ ๊ณ„์‚ฐ: ๊ฐ ๋ถ€์„œ๋ณ„ ์ด ๊ธ‰์—ฌ๋ฅผ ๊ณ„์‚ฐํ•˜๊ณ  ์‹ถ์„ ๋•Œ
    SELECT department, SUM(salary) FROM employees GROUP BY departmentl
    

    ์ด ์ฟผ๋ฆฌ๋Š” โ€˜employeesโ€™ ํ…Œ์ด๋ธ”์—์„œ โ€˜departmentโ€™ ๋ณ„๋กœ ๊ทธ๋ฃนํ™”ํ•˜๊ณ , ๊ฐ ๊ทธ๋ฃน์˜ โ€˜salaryโ€™ ํ•ฉ๊ณ„๋ฅผ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.

  • ๊ทธ๋ฃน๋ณ„ ํ‰๊ท  ๊ณ„์‚ฐ: ๊ฐ ์ œํ’ˆ ์นดํ…Œ๊ณ ๋ฆฌ๋ณ„ ํ‰๊ท  ๊ฐ€๊ฒฉ์„ ๊ณ„์‚ฐํ•˜๊ณ  ์‹ถ์„ ๋•Œ
    SELECT category, AVG(price) FROM products GROUP BY category;
    

    ์ด ์ฟผ๋ฆฌ๋Š” โ€˜productโ€™ ํ…Œ์ด๋ธ”์—์„œ โ€˜categoryโ€™ ๋ณ„๋กœ ๊ทธ๋ฃนํ™”ํ•˜๊ณ , ๊ฐ ๊ทธ๋ฃน์˜ price ํ‰๊ท ์„ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.

  • ๊ทธ๋ฃน๋ณ„ ๋ฐ์ดํ„ฐ ์ˆ˜ ๊ณ„์‚ฐ: ๊ฐ ๋ถ€์„œ์— ์†ํ•œ ์ง์› ์ˆ˜๋ฅผ ์„ธ๊ณ  ์‹ถ์„ ๋•Œ
    SELECT department, COUNT(*) FROM employees GROUP BY department;
    

    ์ด ์ฟผ๋ฆฌ๋Š” โ€˜employeesโ€™ ํ…Œ์ด๋ธ”์—์„œ โ€˜departmentโ€™ ๋ณ„๋กœ ๊ทธ๋ฃนํ™”ํ•˜๊ณ , ๊ฐ ๊ทธ๋ฃน์˜ ์ง์› ์ˆ˜๋ฅผ ์„ธ์–ด ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

โ€˜GROUP BYโ€™ ์ ˆ์˜ ํŠน์ง•

  • ๋ฐ์ดํ„ฐ๋ฅผ ๊ทธ๋ฃนํ™”ํ•˜๊ณ  ๊ฐ ๊ทธ๋ฃน์— ๋Œ€ํ•œ ์ง‘๊ณ„๋ฅผ ์ˆ˜ํ–‰ํ•˜์—ฌ, ๋ฐ์ดํ„ฐ์˜ ์š”์•ฝ ์ •๋ณด๋ฅผ ์ œ๊ณตํ•ฉ๋‹ˆ๋‹ค.
  • ์—ฌ๋Ÿฌ ์—ด์„ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃนํ™”ํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ, ์ด ๊ฒฝ์šฐ ์„ ํƒ๋œ ๋ชจ๋“  ์—ด์˜ ์กฐํ•ฉ์— ๋”ฐ๋ผ ๋ฐ์ดํ„ฐ๊ฐ€ ๊ทธ๋ฃนํ™”๋ฉ๋‹ˆ๋‹ค.
  • ์ง‘๊ณ„ ํ•จ์ˆ˜์™€ ํ•จ๊ป˜ ์‚ฌ์šฉ๋˜์–ด, ๊ฐ ๊ทธ๋ฃน๋ณ„๋กœ ํ•จ๊ณ„, ํ‰๊ท , ์ตœ์†Œ๊ฐ’, ์ตœ๋Œ€๊ฐ’ ๋“ฑ์„ ๊ณ„์‚ฐํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
  • โ€˜HAVINGโ€™ ์ ˆ๊ณผ ํ•จ๊ป˜ ์‚ฌ์šฉํ•˜์—ฌ, ์ง‘๊ณ„ ๊ฒฐ๊ณผ์— ๋Œ€ํ•œ ์กฐ๊ฑด์„ ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋Š” โ€˜WHEREโ€™ ์ ˆ๊ณผ ์œ ์‚ฌํ•˜์ง€๋งŒ, โ€˜GROUP BYโ€™ ๋กœ ๊ทธ๋ฃนํ™”๋œ ๊ฒฐ๊ณผ์— ๋Œ€ํ•ด ์กฐ๊ฑด์„ ์ •์šฉํ•˜๋Š” ์ ์ด ๋‹ค๋ฆ…๋‹ˆ๋‹ค.

์‚ฌ์šฉ ์‹œ ๊ณ ๋ ค์‚ฌํ•ญ

  • โ€˜GROUP BYโ€™ ๋ฅผ ์‚ฌ์šฉํ•  ๋•Œ๋Š” ์„ ํƒ๋œ ์—ด์ด โ€˜SELECTโ€™ ์ ˆ์— ํฌํ•จ๋˜์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋ ‡์ง€ ์•Š์€ ๊ฒฝ์šฐ, SQL ์ฟผ๋ฆฌ๊ฐ€ ์˜ˆ์ƒ๋Œ€๋กœ ์ž‘๋™ํ•˜์ง€ ์•Š์„ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
  • ๋Œ€๊ทœ๋ชจ ๋ฐ์ดํ„ฐ์…‹์—์„œ โ€˜GROUP BYโ€™ ๋ฅผ ์‚ฌ์šฉํ•  ๋•Œ๋Š” ์ฟผ๋ฆฌ์˜ ์„ฑ๋Šฅ์„ ๊ณ ๋ คํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์ ์ ˆํ•œ ์ธ๋ฑ์Šค ์‚ฌ์šฉ๊ณผ ๋ฐ์ดํ„ฐ ๊ตฌ์กฐ์˜ ์ตœ์ ํ™”๊ฐ€ ์„ฑ๋Šฅ์— ํฐ ์˜ํ–ซ์„ ๋ฏธ์น  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

โ€˜GROUP BYโ€™ ์ ˆ์€ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถ„์„ํ•˜๊ณ  ์š”์•ฝ ์ •๋ณด๋ฅผ ์–ป๊ธฐ ์œ„ํ•œ ๊ฐ•๋ ฅํ•œ ๋„๊ตฌ๋กœ, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋‚ด์—์„œ ์˜๋ฏธ ์žˆ๋Š” ์ธ์‚ฌ์ดํŠธ๋ฅผ ๋„์ถœํ•˜๋Š” ๋ฐ ํฌ๊ฒŒ ๊ธฐ์—ฌํ•ฉ๋‹ˆ๋‹ค.