background

SQL Group By Clause

PLEASE NOTE: This tutorial comes from my FREE video course called SQL Boot Camp.

-- drop table posts;
create table posts (
  id integer,
  title character varying(100),
  content text,
  published_at timestamp without time zone,
  type character varying(100)
);

insert into posts (id, title, content, published_at, type)
values (100, 'Intro to SQL', 'Epic SQL Content', '2018-01-01', 'SQL');
insert into posts (id, title, content, published_at, type)
values (101, 'Intro to PostgreSQL', 'PostgreSQL is awesome!', now(), 'PostgreSQL');
insert into posts (id, title, content, type)
values (102, 'Intro to SQL Where Clause', 'Easy as pie!', 'SQL');
insert into posts (id, title, content, type)
values (103, 'Intro to SQL Order Clause', 'What comes first?', 'SQL');
insert into posts (id, title, content, type)
values (104, 'Installing PostgreSQL', 'First things first.', 'PostgreSQL');

select * from posts;
select count(*) from posts;

-- Basic group by
select count(*) from posts group by published_at;
select published_at, count(*) from posts group by published_at;

select count(*) from posts group by type;
select type, count(*) from posts group by type;

-- Adding additional columns to the output
select type, count(*), title from posts group by type;
select type, count(*), title from posts group by type, title;
select type, count(*), max(title) from posts group by type;

select type, count(*), published_at from posts group by type, published_at;

select type, count(*) from posts group by type order by type;

-- Putting it all together
select type, count(*)
from posts
where published_at is null
group by type
order by type;

Please go ahead and leave a comment below if you have any questions about this tutorial.