CODEWITHSUNDEEP
mysqlselectcount
Arkadiusz G.
Arkadiusz G.

Reputation: 1084

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! :)

Upvotes: 0

Views: 5223

Answers (1)

Solarflare
Solarflare

Reputation: 11116

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

Upvotes: 3

Related Questions