CODEWITHSUNDEEP
mysqlsqlselectcount
NationWidePants
NationWidePants

Reputation: 447

Conditional SELECT based COUNT

I have a table with two variables that occur multiple times, each, for the occurrence of each:

i.e.

Customer        City
Bob             New York
Bob             Denver
Bob             Denver
Greg            Denver
Greg            Denver
Greg            New York

Example: 

SELECT Customer FROM tableName WHERE COUNT( SELECT Customer,City FROM tableName)=1;

I want to get unique cities for each unique customer name and count how many cities that customer has. How do you do this in mysql?

Upvotes: 1

Views: 57

Answers (2)

Alberto Martinez
Alberto Martinez

Reputation: 2670

I'm not sure if you want to get the repetitions of a city for each customer or the total number of different cities so I'm going to put both values:

select Customer, City, count(City) as CityCount, TotalDistinctCities
from CustomerCities
join (select Customer as cust, count(distinct City) as TotalDistinctCities
  from CustomerCities
  group by Customer)
as CityCount on cust=Customer   
group by Customer, City, TotalDistinctCities

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

You can use the distinct modified in your count call:

SELECT   customer, COUNT(DISTINCT city)
FROM     tablename
GROUP BY customer

Upvotes: 1

Related Questions