MYSQL Select count with IF

Question

I want get all users with a number of orders with division status

MY QUERY:

SELECT 
    users.id as "Id", 
    users.firstname as "Firstname", 
    users.lastname as "Lastname",
    COUNT(IF(orders.status = 0, 1, 0)) as "Status #0",
    COUNT(IF(orders.status = 1, 1, 0)) as "Status #1",
    COUNT(IF(orders.status = 2, 1, 0)) as "Status #2",
    COUNT(IF(orders.status = 3, 1, 0)) as "Status #3",
    COUNT(IF(orders.status = 4, 1, 0)) as "Status #4",
    COUNT(IF(orders.status = 5, 1, 0)) as "Status #5",
    COUNT(IF(orders.status = 6, 1, 0)) as "Status #6",
    COUNT(IF(orders.status = 7, 1, 0)) as "Status #7",
    COUNT(IF(orders.status = 8, 1, 0)) as "Status #8"
FROM 
    users 
    LEFT JOIN orders ON orders.idu = users.id 
WHERE 
    users.register_complete = 1 
GROUP BY 
    users.id 
ORDER BY 
    users.date_register DESC

Result:

All status is to same.... where is problem? Thanks! :)

Change COUNT to SUM and you are done!

Jacobian
– Jacobian

2016-09-29 08:33:14 +00:00
Commented Sep 29, 2016 at 8:33 — Jacobian
– Jacobian, Commented Sep 29, 2016 at 8:33
Use Sum instead of using Count

Manish
– Manish

2016-09-29 08:33:39 +00:00
Commented Sep 29, 2016 at 8:33 — Manish
– Manish, Commented Sep 29, 2016 at 8:33

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

count(value) will count as 1 if value is something else than null, see documentation:

COUNT(expr)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

So you counted every row, independent from your IF-condition.

Either change your count into sum, or change 0 into null to get the desired result:

...
COUNT(IF(orders.status = 0, 1, null))
...

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 29, 2016 at 8:33

Solarflare

11.2k2 gold badges20 silver badges37 bronze badges

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

2 Comments

Arkadiusz G. Over a year ago

Great news! Thanks you!

fall Over a year ago

note sum(IF(orders.status = 0, 1, 0)) will return null instead of 0 if there are no rows matching the where conditions, which is probably not what you want. So I recommend using count()

Collectives™ on Stack Overflow

MYSQL Select count with IF

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related