Jacek Krawczyk Jacek Krawczyk - 2 months ago 6
SQL Question

How to use all records from another table as counting columns?

I have 4 tables:

location:

location_id name
------------------------
1 France


device:

device_id location_id model_id
-------------------------------------
1 1 1
2 1 2
3 1 3


model:

model_id family_id name
-------------------------------------
1 1 C-max
2 1 S-max
3 2 Vectra


and family:

family_id name
---------------------
1 Ford
2 Opel


I need to build a complicated SQL query now. As the result, I would like to receive this:

location_id name Ford Opel
------------------------------------------
1 France 2 1


Is it possible to do it in SQL at all? I see there there problems:


  • About using other table records as columns in the query

  • About nested tables

  • About counting the elements (
    count
    function?)



Any comments/reference materials will be for me helpful. I do not await the final code.

Answer

Thank you all for your helpful tips.

I solved my problem using the static method and the code published by @Matt. Because somebody else may looking for the solution, I paste here my working query for PostgreSQL:

SELECT DISTINCT t.location_id, t.name, SUM(t.ford) AS ford, SUM(t.opel) as opel
FROM(
SELECT l.location_id, l.name, 
(SELECT COUNT(m.family_id) WHERE m.family_id = '1') AS ford, 
(SELECT COUNT(m.family_id) WHERE m.family_id = '2') AS opel 
FROM location l
INNER JOIN device d ON l.location_id = d.location_id
INNER JOIN model m ON d.model_id = m.model_id
INNER JOIN family f ON m.family_id = f.family_id
GROUP BY l.location_id, l.name, m.family_id
) t
GROUP BY t.location_id;