# Stock Return as Zero due to unsold

At the moment I'm bit stuck and need advice, I have looked up the 'ifnull' but nothing I can find 'ifnull' along with join tables.

``````SELECT Ifnull((SELECT tbl_sku_units_order.sku         AS 'sold_sku',
tbl_list_sku.sku                AS 'list_sku',
tbl_sku_units_order.units_ordered,
COALESCE(Sum(units_ordered), 0) AS 'Today_Sold'
FROM   tbl_list_sku
INNER JOIN tbl_sku_units_order
ON tbl_sku_units_order.sku = tbl_list_sku.sku
GROUP  BY tbl_list_sku.sku
HAVING units_ordered IS NOT NULL
AND Length(units_ordered) > 0
ORDER  BY units_ordered DESC), '0');
``````

so I have tried different function, at the moment I am using this

``````SELECT
tbl_sku_units_order.sku AS 'sold_sku',
tbl_list_sku.sku AS 'list_sku',
tbl_sku_units_order.units_ordered,
COALESCE(SUM(units_ordered), 0) AS 'Today_Sold'
FROM
tbl_list_sku
INNER JOIN
tbl_sku_units_order ON tbl_sku_units_order.sku = tbl_list_sku.sku
GROUP BY tbl_list_sku.sku
HAVING units_ordered IS NOT NULL
AND LENGTH(units_ordered) > 0
ORDER BY units_ordered DESC
``````

in the end, I got the result what they sold

but we have 100 SKU are unsold, but they are not displayed at all, as I would like to see the 100 unsold SKU displayed as '0' as two different table on it. Maybe I am missing something?

• At a glanse, check if left join instead of inner join will do May 1, 2018 at 16:30
• Done, Left Join or Right Join, both got the same result as above. I need to look bigger picture again. May 2, 2018 at 8:44
• Hint: `CREATE TABLE` statements and `INSERT` statements for sample data that demonstrates the problem, will increase the probability that you will get an answer for your question. May 2, 2018 at 8:51
• Go it, Thank you so much. after few tweaks on insert and changing function on IFNULL and Join Table May 2, 2018 at 9:33
• Can a `sku` value appear many times in `tbl_sku_units_order`? May 3, 2018 at 9:22

I would make the following changes, to solve the issue and fix other minor problems with the query:

Solving the issue:

• use `LEFT` instead of `INNER` join, in order to get results for skus that don't have orders.
• remove the `HAVING` clause as it would essentially cancel the previous change.
• use `COALESCE()` or `IFNULL()` to get 0 instead of NULL for these skus.

`GROUP BY` issue:

• use `SUM()` or `MIN()` or some other aggregate for all columns in the `SELECT` that are not in `GROUP BY`.

If you use `IFNULL(tbl_sku_units_order.units_ordered, '0') AS 'unit_order'` for example, when the group by is `GROUP BY tbl_list_sku.sku`, you may have many rows - with different `units_ordered` values - in the orders table. Which one should be shown?

Now MySQL allows that (in versions before 5.7) but the result is indeterminate (any of those values may be shown). The bug/feature has been fixed in version 5.7 but it's good to use correct code, first to get sane results and second so it won't break when you upgrade.

• use aliases for table names
• use back-quotes or double-quotes for column aliases, or just not put spaces in names/aliases so everything can be typed without quotes.

The query:

``````SELECT
ls.sku                                AS list_sku,
COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku,
COALESCE( SUM(suo.units_ordered), 0 ) AS today_sold
FROM   tbl_list_sku AS ls
LEFT JOIN tbl_sku_units_order AS suo
ON suo.sku = ls.sku
GROUP  BY ls.sku
ORDER  BY today_sold DESC ;
``````
• yeah, make sense at all. it's does easy to read code compare from mine May 3, 2018 at 9:23
• i have new question but i am going post it in different page now, it bit similar as this but add is date, as i have two different date and i want them goes to on each coloum. May 3, 2018 at 9:24

Correct function and working

``````    SELECT
tbl_list_sku.sku AS 'sku_List',
IFNULL(tbl_sku_units_order.sku, 'Not Sold') AS 'sold_sku',
IFNULL(tbl_sku_units_order.units_ordered, '0') AS 'unit_order',
IFNULL(units_ordered, '0') AS 'Today sold '
FROM
tbl_sku_units_order
RIGHT JOIN
tbl_list_sku ON tbl_list_sku.sku = tbl_sku_units_order.sku
GROUP BY tbl_list_sku.sku
ORDER BY units_ordered DESC
``````

• You forgot to use `SUM()` May 2, 2018 at 9:40
• Yeah, done it. thanks for gentle reminders. May 3, 2018 at 9:05