Pretty basic SQL query involving count

Question

Let's say I have the following schema

Company:
-> company_id
-> company_name

Building_to_company:
-> building_id
-> company_id

So each building has its own id as well as a company id which relates it to a single company.

the following query gives two columns -- one for the company name, and then its associated buildings.

SELECT company.company_name, building_to_company.building_id 
FROM company, building_to_company 
WHERE company.company_id = building_to_company.company_id;

The returned table would look something like this:

Company Name | Building Id
Smith Banking  2001
Smith Banking  0034
Smith Banking  0101
Smith Banking  4055
Reynolds       8191
TradeCo        7119
TradeCo        8510

So that's all simple enough.

But I need to do something a bit different. I need 2 columns. One for the company name and then on the right the number of buildings it owns. And then for a little extra challenge I only want to list companies with 3 or less buildings.

At this point the only real progress I've made is coming up with the query above. I know I some how have to use count on the building_id column and count the number of buildings associated with each company. And then at that point I can limit things by using something like WHERE x < 4

Answer 1

You've basically got it in words already. Assuming company_name is unique, all you have to add to your explanation to get it to work is a GROUP BY clause:

SELECT company.company_name, COUNT(building_to_company.building_id)
FROM company
INNER JOIN building_to_company 
    ON company.company_id = building_to_company.company_id
GROUP BY company.company_name

(SQL Fiddle demo of this query in action)

---

To limit it to companies with 3 or less buildings, the key is you have to use a HAVING clause and not WHERE. This is because you want to filter based on the results of an aggregate (COUNT); simply put, WHERE filters come before aggregation and HAVING come after:

SELECT company.company_name, COUNT(building_to_company.building_id)
FROM company
INNER JOIN building_to_company 
    ON company.company_id = building_to_company.company_id
GROUP BY company.company_name
HAVING COUNT(building_to_company.building_id) < 4

(SQL Fiddle demo of this query in action)