MySQL Query - Adding up columns & rows

Question

I currently have two tables, one is products and the other is options (colour, size, etc). If no options are specified for the product, it puts the stock against the product table Otherwise it will put the stock against the option table

Tables look a bit like this:

Product table: productid, name, sku, stock

Option table: optionid, productid, sku, stock

I want to pull the data from both tables, and add up the "stock"

So far I have this:

SELECT `product`.`productid`, `product`.`name`, `product`.`sku`,

( SUM(`product`.`stock`) ) + ( SUM(`option`.`stock`) ) AS `stock`

FROM `product`

LEFT JOIN `option` ON `product`.`productid` = `option`.`productid`

GROUP BY `productid`

Which displays the data how I want but the issue is with the stock. For a product that has options specified, it adds them up nicely. If the product doesn't have an option, it just displays "NULL"

Results enter image description here

MatBailie · Accepted Answer · 2012-01-05 13:21:16Z

1

To deal with the NULL problem, you need to wrap the SUM in a COALESCE.

 SUM(`product`.`stock`) + COALESCE(SUM(`option`.`stock`), 0) AS `stock`

This is because SUM(NULL) yields NULL, and x + NULL = NULL. Using COALESCE changes the NULL into a 0, giving x + 0 which equals x.

I can also see a potential issue.

If one product can have many options, the LEFT JOIN will cause duplication in the product records, artificially inflating your stock count.

For example...

product id   stock  |  option id   stock
    1          5           1         1
    1          5           2         2
    2          7           3         1
    2          7           4         2
    2          7           5         3

Although the stock for product 1 is 5, the join makes it look like 10. and for product 2, which has 7 stock, it looks like 21.

The solution is to GROUP BY for the options separately.

SELECT
  `product`.`productid`,
  `product`.`name`,
  `product`.`sku`,
  `product`.`stock` + COALESCE(`option`.`stock`, 0)  AS `stock`
FROM
  `product`
LEFT JOIN
  (SELECT `productid`, SUM(`stock`) AS stock FROM `option` GROUP BY `productid`) as `option`
    ON `option`.`productid` = `product`.`productid`

edited Jan 5, 2012 at 13:21

answered Jan 5, 2012 at 13:13

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

1 Comment

Rozée Over a year ago

I replicated what you said, and the accepted answer didn't duplicate the stock levels. As soon as a option is specified against a product, the stock on the product gets set to zero and gets disabled. So this shouldn't be a problem.

Eugen Rieck · Accepted Answer · 2012-01-05 13:12:43Z

0

SELECT `product`.`productid`, `product`.`name`, `product`.`sku`,

IFNULL(SUM(`product`.`stock`),0) + IFNULL(SUM(`option`.`stock`),0) AS `stock`

FROM `product`

LEFT JOIN `option` ON `product`.`productid` = `option`.`productid`

GROUP BY `productid`

answered Jan 5, 2012 at 13:12

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Comments

aF. · Accepted Answer · 2012-01-05 13:25:47Z

0

Try this:

SELECT `product`.`productid`, `product`.`name`, `product`.`sku`,

( SUM(`product`.`stock`) ) + ( CASE WHEN `option`.`stock` = NULL THEN 0 ELSE SUM(`option`.`stock`) ) AS `stock`

FROM `product`

LEFT JOIN `option` ON `product`.`productid` = `option`.`productid`

GROUP BY `productid`

The problem is that when you try to add NULL the result is NULL

edited Jan 5, 2012 at 13:25

answered Jan 5, 2012 at 13:15

aF.

67k45 gold badges141 silver badges200 bronze badges

1 Comment

Rozée Over a year ago

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EKSE SUM(option.stock) ) AS stock FROM product LEFT JOIN option ON `' at line 3

Petar Sabev · Accepted Answer · 2012-01-05 13:31:10Z

0

Try this statement:

SELECT `product`.`productid`, `product`.`name`, `product`.`sku`,

IF(`option`.`stock`, SUM(`option`.`stock`), SUM(`product`.`stock`) ) AS `stock`

FROM `product`

LEFT JOIN `option` ON `product`.`productid` = `option`.`productid`

GROUP BY `productid`

I've put IF condition in the select. Edited ! To sum option.stock if available

edited Jan 5, 2012 at 13:31

answered Jan 5, 2012 at 13:12

Petar Sabev

8686 silver badges12 bronze badges

1 Comment

Rozée Over a year ago

It seems to ignore adding up my stock off the options table.

user359040 · Accepted Answer · 2012-01-05 13:34:07Z

0

An alternative approach:

select `productid`, `name`, `sku`, sum(`stock`) `stock`
from (select `productid`, `name`, `sku`, `stock` FROM `product`
      union all
      select `productid`, '' `name`, '' `sku`, `stock` FROM `option`) v
GROUP BY `productid`

answered Jan 5, 2012 at 13:34

user359040

Collectives™ on Stack Overflow

MySQL Query - Adding up columns & rows

5 Answers 5

1 Comment

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related