CODEWITHSUNDEEP
mysqlsqlcountconditional-statements
user3363032
user3363032

Reputation: 1

how to display different counts with different conditions?

I have to display two different counts with different conditions, first column shows the number of tasks (COUNT(PROJECTWORKPLAN.TASKS)) and the other one is the number of delayed tasks which i get the logic but don't know how to type the statement.

the PROJECTWORKPLAN table has these columns which can be helpful for determining the delayed 

tasks:
SDate - Start Date
ASDate - Actual Start Date
EDate - End Date
AEDate - Actual End Date

Upvotes: 0

Views: 74

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

This question is tagged "mysql". Although I like the case statement version (because it uses ANSI standard syntax), MySQL has a great convenience: it treats boolean values as integers. So, "true" is 1 and "false" is 0. So you can do this with the simpler syntax of:

SELECT COUNT(*) total,
       SUM(AEDate > EDate) delayed
FROM ...

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

I guess delayed will be defined as if actual end date is greater than task end date then task is delayed so for this you can use count() for all tasks and sum() with case for delayed tasks

SELECT
COUNT(PROJECTWORKPLAN.TASKS) `all_tasks`,
SUM(CASE WHEN AEDate >EDate THEN 1 ELSE 0) `delayed_tasks`
FROM PROJECTWORKPLAN
GROUP BY PROJECTWORKPLAN.id

Upvotes: 2

nosid
nosid

Reputation: 50094

Use the aggregate function SUM in combination with an IF expression to calculate sub counts.

SELECT
    COUNT(1) total,
    SUM(IF(AEDate>EDate,1,0)) delayed
FROM ...

Upvotes: 3

Related Questions