1

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 enter image description here

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?

5
  • 1
    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

2 Answers 2

1

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.

Easier to read code:

  • 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 ; 
2
  • 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
0

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.