CODEWITHSUNDEEP
sqloracle-databasejoin
Steve Shields
Steve Shields

Reputation: 37

SQL count the number of foods supplied by each supplier even if they did not supply anything

I am trying to alter the code below to also include suppliers who did not supply anything from the l_foods table. I got it to display them if they do supply food, but I cannot figure out how to display the rest with a 0 in the number of foods column. I thought the left join would help with that. I'm not sure where to go from here any help would be appreciated.

SELECT 
    a.supplier_id ,
    b.supplier_name ,
    count(a.supplier_id) AS "number of foods" 
FROM  
    l_foods a , 
    LEFT JOIN l_suppliers b ON  a.supplier_id = b.supplier_id  
    GROUP BY  a.supplier_id ,b.supplier_name 
    ORDER BY  a.supplier_id

it gives me the table with the suppliers who have food located in the l_foods table

Asp A Soup Place 3

Cbc Certified Beef Company 2

Frv Frank Reed's VegeTABLEs 2

Jbr Just Beverages 2

Rgf Really Good Foods 2

Vsb Virginia Street Bakery 1

Upvotes: 1

Views: 1285

Answers (4)

Michael Kang
Michael Kang

Reputation: 52867

Try this:

    SELECT a.supplier_id ,a.supplier_name ,sum(case when b.supplier_id is null then 0 else 1 end)) AS "number of foods" 
    FROM l_suppliers a 
    LEFT JOIN l_foods b ON a.supplier_id = b.supplier_id 
    GROUP BY a.supplier_id ,a.supplier_name  
    ORDER BY a.supplier_id

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32402

In order to see all suppliers you need to select from the suppliers table and left join to the foods table

SELECT 
    a.supplier_id ,
    b.supplier_name ,
    count(a.supplier_id) AS "number of foods" 
FROM  
    l_suppliers b , 
    LEFT JOIN  l_foods a ON  a.supplier_id = b.supplier_id  
    GROUP BY  b.supplier_id ,b.supplier_name 
    ORDER BY  b.supplier_id

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want all suppliers even if there are no foods, then you need to start with the suppliers table and left join the foods:

SELECT s.supplier_id, s.supplier_name ,
       count(f.supplier_id) AS "number of foods" 
FROM l_suppliers s LEFT JOIN
     l_foods f 
     ON f.supplier_id = l.supplier_id  
GROUP BY s.supplier_id, s.supplier_name 
ORDER BY s.supplier_id;

Note the following:

  • Changed the table aliases to be abbreviations for the table. This makes the query much easier to follow.
  • Swapped the order of the tables to fix the problem (you could also use RIGHT JOIN, but I much prefer LEFT JOIN)
  • The key columns in the group by and select all come from the suppliers table.

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

Trying using the NVL function (replaces null with 0 the way I've used it here) and using table b in the select list and group by

SELECT b.supplier_id,
       b.supplier_name,
       nvl(count(a.supplier_id), 0) AS "number of foods"
  FROM l_foods a
  LEFT JOIN l_suppliers b
    ON a.supplier_id = b.supplier_id
 GROUP BY b.supplier_id, b.supplier_name
 ORDER BY b.supplier_id

Upvotes: 1

Related Questions