Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgreSQL Query - Count column values matching on two columns

Tags:

sql

postgresql

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 Data from the Tables

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

CUSTOMER
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
like image 554
openV Avatar asked Jan 14 '11 11:01

openV


People also ask

How do I match two column values in SQL?

In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared.

How do I compare two columns in the same table?

Comparison of columns in the same table is possible with the help of joins. Here we are comparing all the customers that are in the same city using the self join in SQL. Self-join is a regular join where a table is joined by itself. Similarly, a table may be joined with left join, right join, inner join, and full join.

How do I check if two columns have the same value in mysql?

Find duplicate values in multiple columns In this case, you can use the following query: SELECT col1, COUNT(col1), col2, COUNT(col2), ... FROM table_name GROUP BY col1, col2, ... HAVING (COUNT(col1) > 1) AND (COUNT(col2) > 1) AND ...


2 Answers

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;
like image 188
athspk Avatar answered Oct 13 '22 00:10

athspk


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
like image 23
Andomar Avatar answered Oct 12 '22 22:10

Andomar