Mysql DISTINCT and COUNT query

Question

im having issues getting a query to output how i want,

SELECT 
    `orders`.`item_id`, 
    `products`.`item_code`, 
    `products`.`item_name`, 
    `orders`.`quantity` 
FROM 
    `orders` 
    JOIN `products` ON `orders`.`item_id` = `products`.`id` 
    JOIN `suppliers` ON `products`.`supplier_ref` = `suppliers`.`supplier_ref` 
WHERE 
    `suppliers`.`id` = 159 
    AND `orders`.`order_status` = 'NOTED'

which is returning the results:

item_id item_code item_name            quantity
1271    RA001G    Green Mop Bucket 12L 2
1270    RA001     Blue Mop Bucket 12L  1
1270    RA001     Blue Mop Bucket 12L  1

but i would like it to bring back distinct item_id with the quantity added together how ever when i've tried to add distinct and count i end up only have one line returned.

You want to see " 1270 RA001 Blue Mop Bucket 12L 2" for the output? — Eray Balkanli
– Eray Balkanli, Commented Feb 12, 2016 at 15:16

Jan · Accepted Answer · 2016-02-12 15:16:06Z

2

If you want to sum up the quantities for same items, try grouping over item_id. Like this:

SELECT 
    `orders`.`item_id`, 
    `products`.`item_code`, 
    `products`.`item_name`, 
    sum(`orders`.`quantity`) as quantity, 
FROM 
    `orders` 
    JOIN `products` ON `orders`.`item_id` = `products`.`id` 
    JOIN `suppliers` ON `products`.`supplier_ref` = `suppliers`.`supplier_ref` 
WHERE 
    `suppliers`.`id` = 159 
     AND `orders`.`order_status` = 'NOTED'
  GROUP BY `orders`.`item_id`, 
      `products`.`item_code`, 
      `products`.`item_name`

answered Feb 12, 2016 at 15:16

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ajking11 Over a year ago

Brilliant thank you so much, i was grouping by item_id, but was then doing COUNT() instead of SUM() which must have been throwing it off

geeksal · Accepted Answer · 2016-02-12 15:20:53Z

0

Please use group by clause

 SELECT 
`orders`.`item_id`, 
`products`.`item_code`, 
`products`.`item_name`, 
 sum(`orders`.`quantity`) as quantity
 FROM 
  `orders` 
   JOIN `products` ON `orders`.`item_id` = `products`.`id` 
   JOIN `suppliers` ON `products`.`supplier_ref` =   `suppliers`.`supplier_ref` 
WHERE 
 `suppliers`.`id` = 159 AND `orders`.`order_status` = 'NOTED'
group by 
 `orders`.`item_id`;

answered Feb 12, 2016 at 15:20

geeksal

5,0163 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

Mysql DISTINCT and COUNT query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related