Jacek Krawczyk Jacek Krawczyk - 1 year ago 65
SQL Question

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

I have 4 tables:


location_id name
1 France


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


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 (

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

Answer Source

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
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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download