How to Group 0 with NULL in mysql select

Question

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?

I'm not sure it makes sense to say you want grouping by website and then include order ids in the result. That's going to force grouping on order id too I think — topsail
– topsail, Commented May 17, 2024 at 16:36
If you give the alias a different name from the column, you can use GROUP BY <alias> — Barmar
– Barmar, Commented May 17, 2024 at 16:57
The inclusion of p.orderid in your select list makes your query nondeterministic. Configured properly (with ONLY_FULL_GROUP_BY enabled) you should be getting an error. Please read MySQL Handling of GROUP BY. — user1191247
– user1191247, Commented May 17, 2024 at 18:01

Barmar · Accepted Answer · 2024-05-17 17:01:29Z

2

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)

answered May 17, 2024 at 17:01

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

spalominor · Accepted Answer · 2024-05-17 17:09:01Z

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.

answered May 17, 2024 at 17:09

spalominor

1701 silver badge8 bronze badges

Comments

ArtBindu · Accepted Answer · 2024-05-17 17:01:29Z

-1

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

edited May 17, 2024 at 17:01

answered May 17, 2024 at 16:58

ArtBindu

2,0381 gold badge12 silver badges21 bronze badges

3 Comments

Barmar Over a year ago

They're already using IFNULL(), which is the same as COALESCE() when there are only 2 arguments.

Barmar Over a year ago

You need to assign an alias in the subquery.

ArtBindu Over a year ago

Yes, you're right @Barmar. Your approach is best...

Collectives™ on Stack Overflow

How to Group 0 with NULL in mysql select

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related