Mysql Question posted in
The official MySQL documentation can be found here.

How to Group 0 with NULL in mysql select

I know there are many variants of this question, but none seem to address my issue.

I have a page reporting website order stats. One of the variables it considers is which ‘website’ a customer used.
The website ids are integers, but for historical reasons, one of them is stored as either 0 or NULL.
I need to group them together in my query.

I know I could update all NULL entries to 0 in that row of the db, but a lot of changes would need to be made to prevent more being added.

My original query was:

SELECT o.website, p.orderid, count(p.orderid) ordercount
FROM gdd_order as o, gdd_process as p 
WHERE o.orderid = p.orderid 
AND p.prog_return >= '2024-03-01' 
AND p.prog_return < '2024-03-31'  
GROUP BY o.website 
ORDER BY FIELD(o.website,0,5,1,2,3,4)

which returns

website orderid ordercount
(NULL) 92914 76
0 93256 48
5 92916 74
1 92908 136

I tried:

SELECT ifnull(o.website, 0) website, p.orderid, count(p.orderid) ordercount
FROM gdd_order as o, gdd_process as p 
WHERE o.orderid = p.orderid 
AND p.prog_return >= '2024-03-01' 
AND p.prog_return < '2024-03-31'  
GROUP BY o.website 
ORDER BY FIELD(o.website,0,5,1,2,3,4)

which makes NULL zero, but doesn’t group the ‘null 0’ row in with the ‘0’ row:

website orderid ordercount
0 92914 76
0 93256 48
5 92916 74
1 92908 136

How do I combine the ‘0’ and ‘NULL 0’ data in a single row?

Tags:

3

Answers


  1. 0

    Use COALESCE() function which returns the first non-null value in a list.

    https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce

    Update your query like:

    SELECT o.website, p.orderid, count(p.orderid) ordercount
FROM (SELECT COALESCE(website, 0), [OTHER COLUMS] FROM gdd_order) o, gdd_process as p 
WHERE o.orderid = p.orderid 
AND p.prog_return >= '2024-03-01' 
AND p.prog_return < '2024-03-31'  
GROUP BY o.website 
ORDER BY FIELD(o.website,0,5,1,2,3,4)

    It’s should work

    SELECT COALESCE(website, 0), [OTHER COLUMS] FROM gdd_order;

    This query will change your table from

    website orderid ordercount
    (NULL) 92914 76
    0 93256 48
    5 92916 74
    1 92908 136

    to

    website orderid ordercount
    0 92914 76
    0 93256 48
    5 92916 74
    1 92908 136

    Thank You

    Login or Signup to reply.
  2. 0

    Use IFNULL(o.website, 0) consistently throughout the query.

    SELECT ifnull(o.website, 0) website, p.orderid, count(p.orderid) ordercount
FROM gdd_order as o, gdd_process as p 
WHERE o.orderid = p.orderid 
AND p.prog_return >= '2024-03-01' 
AND p.prog_return < '2024-03-31'  
GROUP BY IFNULL(o.website, 0)
ORDER BY FIELD(IFNULL(o.website, 0),0,5,1,2,3,4)
    Login or Signup to reply.
  3. 0

    friend. I think you can add this part to combine 0 and NULL

    IFNULL(o.website, 0)

    So, your query:

    SELECT IFNULL(o.website, 0) AS website, p.orderid, COUNT(p.orderid) AS ordercount
FROM gdd_order AS o
JOIN gdd_process AS p ON o.orderid = p.orderid
WHERE p.prog_return >= '2024-03-01' AND p.prog_return < '2024-03-31'  
GROUP BY IFNULL(o.website, 0)
ORDER BY FIELD(IFNULL(o.website, 0), 0, 5, 1, 2, 3, 4);

    Tell if this works with your database, we don’t have sample information to prove it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search