Clorae Clorae - 4 months ago 7
MySQL Question

getting the sum per month based on the data of another column if it is not null hence if it is null then it won't be added to the total

I have

view_dtr
with columns
Undertime
,
LateAM
and
LatePM
I need to get the
TotalUndertime
if either
LateAM
or
LatePM
IS NOT NULL
.

| EmpID | DatePresent | Undertime | LateAM | LatePM |
| 305001 | 2016-04-01 | 00:10:00 | 01:00:00 | 00:12:00 |
| 305001 | 2016-04-02 | 00:15:00 | | |
| 305001 | 2016-04-03 | 00:20:00 | 00:05:00 | |
| 305001 | 2016-04-04 | 00:05:00 | | 00:01:00 |
| 305002 | 2016-04-01 | 00:01:00 | | 00:10:00 |
| 305002 | 2016-04-02 | 00:10:00 | | |
| 305002 | 2016-04-03 | 00:10:00 | 00:02:00 | 00:03:00 |


The
TotalUndertime
for
EmpID = 305001
should only be
00:35:00
and
EmpID = 305002
should be
00:11:00
. I keep on getting the whole sum using the
CASE
statement.

CASE WHEN LateAM IS NOT NULL AND Undertime IS NOT NULL
THEN SEC_TO_TIME(SUM(TIME_TO_SEC(Undertime)))
WHEN LatePM IS NOT NULL AND Undertime IS NOT NULL
THEN SEC_TO_TIME(SUM(TIME_TO_SEC(Undertime)))
ELSE NULL
END AS TotalUndertime
FROM view_dtr
GROUP BY EmpID, MONTH(DatePresent)


Any help and suggestion is greatly appreciated.
sorry for being a newbie.

Answer

You need to do the CASE inside the SUM(), because aggregation occurs after selecting.

You also don't need to filter out Undertime = NULL because those are ignored when summing.

SELECT SEC_TO_TIME(SUM(CASE WHEN LateAM IS NOT NULL OR LatePM IS NOT NULL
                            THEN TIME_TO_SEC(UnderTime)
                       END)) AS TotalUndertime
FROM view_dtr
GROUP BY EmpID, MONTH(DatePresent)
Comments