Menu
I’m trying to combine 2 rows of results into 1 using the below CASE clause. ‘<26’ should only appear once, and the results should be combined.
SELECT CASE org.size WHEN 0 THEN '<26' WHEN 1 THEN '<26' WHEN 2 THEN '26-50' WHEN 3 THEN '51-100' WHEN 4 THEN '101-250' WHEN 5 THEN '251-500' WHEN 6 THEN '501-1000' WHEN 7 THEN '1001-5000' ELSE '5000+' END AS 'Size', COUNT(DISTINCT org.id) AS '# of Companies' FROM org INNER JOIN usr ON usr.orgid = org.id INNER JOIN usr_role ON usr.id = usr_role.usrid WHERE org.deleted = 0 AND usr.brnd = 1 AND usr_role.role = 1 GROUP BY org.size; 
281
Multiple conditions in CASE statementYou can evaluate multiple conditions in the CASE statement.
Yes, i have another table named "styles". And a style has 5-6 different options. For example: style_id 24 has 5 different options (red, green, yellow, ...).
Definition and Usage. The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause.
not equal to (<>, !=) operator. MySQL Not equal is used to return a set of rows (from a table) after making sure that two expressions placed on either side of the NOT EQUAL TO (<>) operator are not equal.
how about this?
SELECT CASE WHEN org.size IN (0, 1) THEN '<26' WHEN org.size = 2 THEN '26-50' WHEN org.size = 3 THEN '51-100' WHEN org.size = 4 THEN '101-250' WHEN org.size = 5 THEN '251-500' WHEN org.size = 6 THEN '501-1000' WHEN org.size = 7 THEN '1001-5000' ELSE '5000+' END AS Size, .... The problem is that you are grouping the records org.size causing <26 at two different groups since they are originally 0 and 1.
This will work,
GROUP BY CASE WHEN org.size IN (0, 1) THEN '<26' WHEN org.size = 2 THEN '26-50' WHEN org.size = 3 THEN '51-100' WHEN org.size = 4 THEN '101-250' WHEN org.size = 5 THEN '251-500' WHEN org.size = 6 THEN '501-1000' WHEN org.size = 7 THEN '1001-5000' ELSE '5000+' END If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
