CODEWITHSUNDEEP
mysqlsubquery
anderlaini
anderlaini

Reputation: 1831

how to use subqueries to COUNT() and SUM() items

I'm trying to get a list of:

StockPart.title   |   QtyAvailable   |   SumOfItemsCost
StockPart.title   |   QtyAvailable   |   SumOfItemsCost
StockPart.title   |   QtyAvailable   |   SumOfItemsCost
StockPart.title   |   QtyAvailable   |   SumOfItemsCost

Trying to explain:

My table structure is

stock_parts
  - id
  - title

stock_items
  - id
  - stock_part_id
  - shipment_id

shipments
  - item_cost

So i'm a bit lost on how to do this. I'm trying things like this:

SELECT
  stock_parts.id,
  stock_parts.title,
  (SELECT COUNT(*) FROM stock_items WHERE
                    stock_items.stock_part_id = stock_parts.id
                    AND stock_items.status = NULL)
                    AS qty_available
FROM
  stock_parts,
  stock_items

Anyone can help?

Upvotes: 1

Views: 71

Answers (2)

Tony Abou Zaidan
Tony Abou Zaidan

Reputation: 104

if you want it grouped:

SELECT
    stock_parts.title,
    COUNT(*),
    SUM(shipments.item_cost) 
FROM
    stock_items
INNER JOIN
    stock_parts
ON
    stock_items.stock_part_id = stock_parts.id
INNER JOIN
    shipments
ON
    shipments.id = stock_items.shipment_id
GROUP BY stock_parts.id

Upvotes: 2

Tony Abou Zaidan
Tony Abou Zaidan

Reputation: 104

This is what you could be doing:

SELECT
    stock_parts.title,
    (
        SELECT COUNT(*) 
        FROM stock_items 
        WHERE stock_items.id = stckItm.id
    ),
    (
        SELECT 
            SUM(item_cost) 
        FROM stock_items 
        INNER JOIN shipments ON shipments.id = stock_items.shipment_id 
        WHERE stock_items.id = stckItm.id
    )
FROM
    stock_items stckItm
inner join
    stock_parts
ON
    stckItm.stock_part_id = stock_parts.id

Upvotes: 2

Related Questions