CODEWITHSUNDEEP
mysqlcase
tomish
tomish

Reputation: 77

CASE statement in mySQL, comparison operator

I don't understand why this query does not work. It hits a file with about 15,000 records.

SELECT emp_id, check_date, case when check_amount <= 300 then check_amount 
end as below_300   FROM `payments`

It should return only those checks that are less than 300 but it returns the entire file all-- 15,000 records. It returns the correct emp_id, check_date but a 'null' in the check amount.

I am using a CASE statement because I want to run several thresholds for checks lower than certain amounts ($300, $1,000, $2,000 etc).

Thanks!

Upvotes: 0

Views: 362

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Here's two possible answers:

If you want to run individual queries for each range:

SELECT 
    emp_id, 
    check_date, 
    check_amount
FROM `payments`
WHERE check_amount <= 300

If you want summary information on the groups you mention.

SELECT 
    `emp_id`,
    `check_date`,
    sum(if(`check_amount` <= 300, 1, 0)) as `num_under_300`,
    sum(if(`check_amount` <= 300, `check_amount`, 0)) as `sum_under_300`,
    sum(if(`check_amount` > 300 AND `check_amount` <= 1000, 1, 0)) as `num_under_1000`,
    sum(if(`check_amount` > 300 AND `check_amount` <= 1000, `check_amount`, 0)) as `sum_under_1000`,
    sum(if(`check_amount` > 1000 AND `check_amount` <= 2000, 1, 0)) as `num_under_2000`,
    sum(if(`check_amount` > 1000 AND `check_amount` <= 2000, `check_amount`, 0)) as `sum_under_2000`,
    sum(if(`check_amount` > 2000, 1, 0)) as `num_over_2000`,
    sum(if(`check_amount` > 2000, `check_amount`, 0)) as `sum_over_2000`
FROM `payments`
GROUP BY `emp_id`,`check_date`

Upvotes: 1

Related Questions