SQL Ninja

Practice       Modules       Leaderboard       Daily Challenge      

Stock Check: Suppliers' Shipment Quantities:

Question:

List all suppliers along with the total quantity of products they've shipped. Display the 'supplier_id', 'supplier_name', and 'total_shipped_quantity'. Include even those suppliers who haven't shipped any products by showing their 'total_shipped_quantity' as 0.

Sample Input Tables:

Suppliers Table:

supplier_id supplier_name contact address
1 TechSource +1234567890 123 Tech Street
2 GadgetFlow +0987654321 456 Gadget Ave
3 Electronix +1122334455 789 Electron Road

Shipments Table:

shipment_id supplier_id product_id quantity date
101 1 10 50 2024-01-01
102 2 20 70 2024-01-02
103 1 30 30 2024-01-03

Sample Output:

supplier_id supplier_name total_shipped_quantity
1 TechSource 80
2 GadgetFlow 70
3 Electronix 0

This output is just for illustrative purposes. The actual output could be different from the one you see above.

SELECT s.supplier_id, s.supplier_name, COALESCE(SUM(sh.quantity), 0) AS total_shipped_quantity
FROM Suppliers s
LEFT JOIN Shipments sh ON s.supplier_id = sh.supplier_id
GROUP BY s.supplier_id, s.supplier_name;

English:

  1. LEFT JOIN: "Including All Suppliers"
    • Initiates a LEFT JOIN between the Suppliers and Shipments tables to ensure all suppliers are included, even those without any shipments.
  2. COALESCE: "Accounting for No Shipments"
    • Utilizes the COALESCE function to substitute any null values in the sum of shipments with 0, catering to suppliers who haven't shipped any products.
  3. GROUP BY: "Summarizing Shipments by Supplier"
    • Groups the results by supplier_id and supplier_name to consolidate shipments data for each supplier.

Hinglish:

  1. LEFT JOIN: "Including All Suppliers"
    • Suppliers aur Shipments tables ke beech ek LEFT JOIN ka aarambh karta hai taaki sabhi suppliers ko shamil kiya ja sake, unko bhi jo kisi bhi shipment me shamil nahi hain.
  2. COALESCE: "Accounting for No Shipments"
    • COALESCE function ka upyog karke shipments ki sum me kisi bhi null values ko 0 ke saath substitute karta hai, un suppliers ke liye jo kisi bhi products ko ship nahi kiya hai.
  3. GROUP BY: "Summarizing Shipments by Supplier"
    • Results ko supplier_id aur supplier_name ke hisaab se group karta hai taaki har supplier ke liye shipments data ko consolidate kiya ja sake.


  Submit Answer


GIF Image