16

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
Lee Goddard
  • 10,680
  • 4
  • 46
  • 63
openV
  • 161
  • 1
  • 11
  • 1
    possible duplicate of [T-SQL: is it possible to specify condition in Count()?](http://stackoverflow.com/questions/1400078/t-sql-is-it-possible-to-specify-condition-in-count) – Guffa Jan 14 '11 at 11:52

2 Answers2

22

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;
athspk
  • 6,722
  • 7
  • 37
  • 51
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
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    Thanks Andomar, it works fine but doesn't give me the zeros in Gold. You guys are fast – openV Jan 14 '11 at 11:57
  • 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)? – Max von Hippel Oct 25 '16 at 08:46