SQLite | Querying Data | GROUP BY and HAVING
GROUP BY groups rows for aggregate calculations; HAVING filters the resulting groups.
Group rows with GROUP BY
SELECT columns FROM table_name GROUP BY column1, column2;
Rows with equal values, or equal combinations of multiple values, form a group.
create table user (name text, gender text, address text);
insert into user values ('devkuma', 'man', 'Seoul');
insert into user values ('kimkc', 'man', 'Busan');
insert into user values ('arikuma', 'woman', 'Seoul');
insert into user values ('happykuma', 'woman', 'Suwan');
insert into user values ('raccoon', 'man', 'Busan');
insert into user values ('mykuma', 'woman', 'Daejeon');
insert into user values ('yourkuma', 'man', 'Seoul');
select count(*) from user; -- 7
select gender, count(*) from user group by gender;
select address, count(*) from user group by address;
select gender, address, count(*) from user group by gender, address;
The gender query returns man|4 and woman|3. The address query returns Busan|2, Daejeon|1, Seoul|3, and Suwan|1. Other aggregate functions can calculate sums or averages per group.
Filter groups with HAVING
WHERE filters rows before grouping, whereas HAVING filters aggregate results after grouping.
SELECT columns FROM table_name WHERE condition GROUP BY columns;
SELECT columns FROM table_name GROUP BY columns HAVING condition;
Only grouped columns and aggregate results can be used appropriately in the HAVING condition. To return addresses with at least two users:
select address, count(*) from user
group by address having count(*) >= 2;
Busan|2
Seoul|3