CODEWITHSUNDEEP
mysqlsql
anderlaini
anderlaini

Reputation: 1831

sql - how to group by joined table

My Schema is:

- StockPart
    id
    title
    ...
- StockItem
    id
    shipment_id
    stock_part_id
    status
    ...
- Shipment
    id
    item_cost
    ...

I'm using this query to get a list of DeviceParts and its information.

SELECT
    `stock_parts`.`id`,
    `stock_parts`.`title`,
    COUNT(stock_items.id) AS qtyAvailable,
    SUM(shipments.item_cost) AS totalCost
FROM
    `stock_parts`
LEFT JOIN `stock_items` ON `stock_items`.`stock_part_id` = `stock_parts`.`id` AND ( `stock_items`.`status` IS NULL )
LEFT JOIN `shipments` ON `shipments`.`id` = `stock_items`.`shipment_id`
GROUP BY
    `stock_parts`.`id`,
    `stock_parts`.`title`,
    `shipments`.`id`
ORDER BY
    `stock_parts`.`title` ASC

So it's repeating items this way:

stockPart.id   |   stockPart.id     |   qtyAvailable   |     totalCost
174            |   ALTO FALANT 5C   |         1        |     1.00
174            |   ALTO FALANT 5C   |        10        |     100.00

The problem is related to StockItem and Shipment... because I StockItems can be related to different Shipments with different item_cost values.

In this case, I'd expect a single with sum values:

        174   |   ALTO FALANT 5C   |   11  |   101.00

How can I do it? Should I group something?

Upvotes: 0

Views: 34

Answers (2)

mrodo
mrodo

Reputation: 583

You need to remove shipments.id from your GROUP BY clauses. You should only do GROUP BY for the fields that you want to select without an aggregate function, which in your case are just stock_parts.id and stock_parts.title.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

shipments.id is not required in aggregation through which you are getting multiple entry as you said :

The problem is related to StockItem and Shipment... because I StockItems can be related to different Shipments with different item_cost values

SELECT `stock_parts`.`id`, `stock_parts`.`title`,
        COUNT(stock_items.id) AS qtyAvailable,
        SUM(shipments.item_cost) AS totalCost
FROM `stock_parts` LEFT JOIN 
     `stock_items` 
      ON `stock_items`.`stock_part_id` = `stock_parts`.`id` AND 
         ( `stock_items`.`status` IS NULL ) LEFT JOIN 
      `shipments` 
      ON `shipments`.`id` = `stock_items`.`shipment_id`
GROUP BY `stock_parts`.`id`,`stock_parts`.`title`
ORDER BY `stock_parts`.`title` ASC;

Upvotes: 2

Related Questions