CODEWITHSUNDEEP
mysql
Jamie Pittock
Jamie Pittock

Reputation: 103

Sum of column for distinct rows

I have the following MySQL table structure

 id          product_id    order_id   order_shipping   shipping_code
  1               23            1            2.00           GB
  2               12            1            2.00           GB
  3               5             2            3.50           GB
  4               6             2            3.50           GB
  5               34            3            3.00           FR
  6               8             3            3.00           FR
  7               4             4            5.00           IN

I'd like to group the rows by the shipping_code and include the total orders and total shipping cost per code. Something like this:

Code    Orders   Cost
 GB       2       5.50
 FR       1       3.00
 IN       1       5.00

I'm struggling with the total cost part. The shipping cost is for the order as a whole, not the product. It's just duplicated in each row relating to the same order. So for order #1 the shipping cost was £2, not £4.

How do I do a sum of that column but only for the distinct order rows?

Upvotes: 0

Views: 29

Answers (1)

Nick
Nick

Reputation: 147146

To get around the duplicated shipping costs for each order you need to group your data by order_id first, and then COUNT the number of orders and SUM the shipping costs:

SELECT code, COUNT(o.shipping) AS orders, SUM(o.shipping) AS cost
FROM (SELECT MIN(shipping_code) AS code, MIN(order_shipping) AS shipping
      FROM orders
      GROUP BY order_id) o
GROUP BY code

Output:

code    orders  cost
GB      2       5.5
FR      1       3
IN      1       5

Demo on dbfiddle

Upvotes: 1

Related Questions