Arkadiusz G. Arkadiusz G. - 2 months ago 6
MySQL Question

MYSQL Select count with IF

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

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

Answer

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))
...
Comments