CODEWITHSUNDEEP
mysql
Pablo
Pablo

Reputation: 1435

IF Else count(*) in MySQL query?

I have view named as view_date_time_records

enter image description here

What I'm trying to do here is count(*) the records whether the hours

is greater than 8 hours or below 6 hours

If 1 row is greater than or equal to (>=) 8 hours this should be counted as 1

and

If I have 1 row that is less than or equal to (<=) 6 hours this should be counted as 0.5

Ex:

I have 3 rows with hours value of 10,10,5

that means, the output is: 2.5

I just want to do this on my query not on my php codes.

I only have simple query like this

SELECT count(ACNo) FROM view_date_time_records WHERE hours >= 8

Still trying to explore the conditional for this kind of query.

Upvotes: 2

Views: 461

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521609

You may use conditional summation here:

SELECT
    SUM(CASE WHEN hours >= 8 THEN 1 ELSE 0 END) AS greater_than_8,
    SUM(CASE WHEN hours <= 6 THEN 0.5 ELSE 0 END) AS less_than_6
FROM view_date_time_records;

The above query would give a table-level report. If instead, for example, you wanted to report these per account, you could try:

SELECT
    ACNo,
    SUM(CASE WHEN hours >= 8 THEN 1 ELSE 0 END) AS greater_than_8,
    SUM(CASE WHEN hours <= 6 THEN 0.5 ELSE 0 END) AS less_than_6
FROM view_date_time_records
GROUP BY
    ACNo;

Edit:

Per the suggestion of @Akina, we can try the below more concise form:

SELECT
    SUM(hours >= 8) AS greater_than_8,
    SUM(hours <= 6) / 2 AS less_than_6
FROM view_date_time_records;

Upvotes: 3

Related Questions