postgreSQL Query - Count column values matching on two columns

Question

I need help creating an SQL query to count rows broken out on two separate columns.

This is the DDL for my Tables:

CREATE TABLE Agency (
  id SERIAL not null,
  city VARCHAR(200) not null,
  PRIMARY KEY(id)
);
CREATE TABLE Customer (
  id SERIAL not null,
  fullname VARCHAR(200) not null,
  status VARCHAR(15) not null CHECK(status IN ('new','regular','gold')),
  agencyID INTEGER not null REFERENCES Agency(id),
  PRIMARY KEY(id)
);

Sample of the AGENCY table:

id	'city'
1	'London'
2	'Moscow'
3	'Beijing'

Sample of the CUSTOMER table:

id	'fullname'	status	agencyid
1	'Michael Smith'	'new'	1
2	'John Doe'	'regular'	1
3	'Vlad Atanasov'	'new'	2
4	'Vasili Karasev'	'regular'	2
5	'Elena Miskova'	'gold'	2
6	'Kim Yin Lu'	'new'	3
7	'Hu Jintao'	'regular'	3
8	'Wen Jiabao'	'regular'	3

I want to count the new customers, regular customers and gold_customers by city.

I need to count separately for ('new','regular','gold'). Here is the output I want:

'city'	new_customers	regular_customers	gold_customers
'Moscow'	1	1	1
'Beijing'	1	2	0
'London'	1	1	0

possible duplicate of T-SQL: is it possible to specify condition in Count()? — Guffa
– Guffa, Commented Jan 14, 2011 at 11:52

athspk · Accepted Answer · 2011-01-14 12:04:57Z

23

I was struggling with the same a couple of weeks ago.
This is what you need.

SELECT 
  Agency.city,
  count(case when Customer.status = 'new' then 1 else null end) as New_Customers,
  count(case when Customer.status = 'regular' then 1 else null end) as Regular_Customers,
  count(case when Customer.status = 'gold' then 1 else null end) as Gold_Customers 
FROM 
  Agency, Customer 
WHERE 
  Agency.id = Customer.agencyID 
GROUP BY
  Agency.city;

edited Jan 14, 2011 at 12:04

answered Jan 14, 2011 at 11:46

athspk

6,7627 gold badges39 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andomar · Accepted Answer · 2011-01-14 11:49:36Z

7

You could group on city, and then sum the number of statuses in each city:

select  city
,       sum(case when c.status = 'new' then 1 end) as New
,       sum(case when c.status = 'regular' then 1 end) as Regular
,       sum(case when c.status = 'gold' then 1 end) as Gold
from    customer c
join    agency a
on      c.agencyid = a.id
group by
        a.city

answered Jan 14, 2011 at 11:49

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

openV Over a year ago

Thanks Andomar, it works fine but doesn't give me the zeros in Gold. You guys are fast

Max von Hippel Over a year ago

A bit late to the game but maybe the difference in zeros is due to sum vs count (which gain a false equivalency due to out returning of 1 vs nothing)?

Collectives™ on Stack Overflow

postgreSQL Query - Count column values matching on two columns

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related