SQL - Group By Distinct Values

Question

My question, is there a faster way to the following query?

I'm using ORACLE 10g

Say i have a table Manufacturer and Car, and i want to count all occurrences of the column 'Car.Name'. here is How i'd do it:

SELECT manuf.Name, COUNT(car1.Name), COUNT(car2.Name), COUNT(car3.Name)
FROM Manufacturer manuf 
LEFT JOIN (SELECT * FROM Car c where c.Name = 'Ferrari1') car1 ON manuf.PK = car1.ManufPK
LEFT JOIN (SELECT * FROM Car c where c.Name = 'Ferrari2') car2 ON manuf.PK = car2.ManufPK
LEFT JOIN (SELECT * FROM Car c where c.Name = 'Ferrari3') car3 ON manuf.PK = car3.ManufPK
GROUP BY manuf.Name

Wanted Results:

Manufacturer | Ferrari1 | Ferrari2 | Ferrari3
----------------------------------------------
Fiat         |    1     |   0      |  5
Ford         |    2     |   3      |  0

I tried this with few LEFT JOINs, and it worked fine. But when i added a lot (like 90+), it was ultra slow (more than 1 minute).

My question, is there a faster way to do this query?

@WoF_Angel which RDBMS do you use? In MySQL you can think about the use of 'GROUP_CONCAT' in MSSQL about 'PIVOT' — Andreas Rohde, Jan 25 '12 at 11:45
[With the right indexes multiple joins will likely perform at least as well as PIVOT/ GROUP BY](http://stackoverflow.com/questions/7448453/sql-server-pivot-vs-multiple-join/7449213#7449213) — Martin Smith, Jan 25 '12 at 11:50
Preferrably, yes. But this will do if there is no other way. — WoF_Angel, Jan 25 '12 at 11:55

score 3 · Accepted Answer · answered Jan 25 '12 at 11:43

If you are happy to see the cars counted down the page, try:

select m.Name manufacturer_name,
       c.Name car_name,
       count(*)
from Manufacturer m
left join Car c 
       on m.PK = c.ManufPK and c.Name in ('Ferrari1','Ferrari2','Ferrari3')
group by m.Name, c.Name

If you need to see individual cars across the page, try:

select m.Name manufacturer_name,
       sum(case c.Name when 'Ferrari1' then 1 else 0 end) Ferrari1_Count,
       sum(case c.Name when 'Ferrari2' then 1 else 0 end) Ferrari2_Count,
       sum(case c.Name when 'Ferrari3' then 1 else 0 end) Ferrari3_Count
from Manufacturer m
left join Car c 
       on m.PK = c.ManufPK and c.Name in ('Ferrari1','Ferrari2','Ferrari3')
group by m.Name

@aF So what? It this not suits the needs - so we need some refined question — Oleg Dok, Jan 25 '12 at 11:47

score 0 · Answer 2 · answered Jan 25 '12 at 11:42

0

SELECT manuf.Name, COUNT(DISTINCT c.Name)
FROM Manufacturer manuf 
LEFT JOIN Car c ON manuf.PK = c.ManufPK
GROUP BY manuf.Name

OR depending on your needs

SELECT manuf.Name, c.Name, COUNT(*) Cnt
FROM Manufacturer manuf 
LEFT JOIN Car c ON manuf.PK = c.ManufPK
GROUP BY manuf.Name, c.Name

PS: Your question is not very clear. Provide some wanted resultset to refine the answer

answered Jan 25 '12 at 11:42

Oleg Dok

21,109
4
45
54

That won't give all counts in the same row. – aF. Jan 25 '12 at 11:45
@MarkBannister See commented answer – Oleg Dok Jan 25 '12 at 11:46

score 0 · Answer 3 · answered Jan 25 '12 at 13:07

You can also try this:

SELECT manuf.Name
     , car1.cnt AS Ferrari1
     , car2.cnt AS Ferrari2
     , car3.cnt AS Ferrari3
FROM 
      Manufacturer AS manuf 
  LEFT JOIN 
      ( SELECT ManufPK, COUNT(*) AS cnt
        FROM Car  
        WHERE Name = 'Ferrari1'
        GROUP BY ManufPK
      ) AS car1 
    ON car1.ManufPK = manuf.PK 
  LEFT JOIN 
      ( SELECT ManufPK, COUNT(*) AS cnt
        FROM Car  
        WHERE Name = 'Ferrari2'
        GROUP BY ManufPK
      ) AS car2 
    ON car2.ManufPK = manuf.PK  
  LEFT JOIN 
      ( SELECT ManufPK, COUNT(*) AS cnt
        FROM Car 
        WHERE Name = 'Ferrari3'
        GROUP BY ManufPK
      ) AS car3 
    ON car3.ManufPK = manuf.PK 
ORDER BY manuf.Name

SQL - Group By Distinct Values

3 Answers3