CODEWITHSUNDEEP
sqlaggregate
Mike D
Mike D

Reputation: 99

SQL Count from two tables

I have a database with information about customers, stores, purchases, products, etc. I'm interested in how many customers live in each city. I found this by:

SELECT city, count(cID) FROM customer GROUP BY city

This gives the following table:

| City          | # |
+---------------+---+
| Amsterdam     | 4 |
| New York City | 2 |
| London        | 1 |

Now I'm not only interested in the cities where customers live. I'd also like to have the cities in which there is a store and where no customers live in the table. Like this:

| City          | # |
+---------------+---+
| Amsterdam     | 4 |
| New York City | 2 |
| London        | 1 |
| Los Angeles   | 0 |

Short, I'd like to count customers that live in each city that occurs in the customers table, the store table or both.

The tables look like this: customer(cID, cName, street, city) and store(sID, sName, street, city)

Does anyone know how to do this?

Upvotes: 0

Views: 64

Answers (3)

MPetrovic
MPetrovic

Reputation: 171

You can do that with UNION ALL

SELECT someColumn,someColumn,NULL
FROM   someTable

UNION ALL

SELECT NULL, NULL, someColumn
FROM   nextTable/orSameTable

Null is place where u will put next result that u want from another query

Upvotes: 0

3N1GM4
3N1GM4

Reputation: 3361

SELECT  c.city, 
        COUNT(DISTINCT c.cID) AS [#]
FROM customer c
GROUP BY c.city

UNION

SELECT  s.city,
        0 AS [#]
FROM store s
LEFT JOIN customer c ON s.city = c.city
WHERE c.cID IS NULL

Upvotes: 1

jarlh
jarlh

Reputation: 44805

Do a LEFT JOIN between store table and the customer table, to also get cities without any customers!

SELECT s.city, count(*)
FROM store s
LEFT JOIN customer c ON s.city = c.city
GROUP BY s.city

Also, as @Akhter Alamin Farhan commented, don't you have a cities table? So instead of storing city names over and over again, just store them once and use cityid in store and customer tables.

Upvotes: 0

Related Questions