Vallavan J - 1 year ago 66

MySQL Question

this the query:

How to get the value of three select query

Name,HOURS ENTERED',HOURS APPROVED,HOURS REJECTED

`SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS ENTERED' FROM labors l, emps e`

WHERE l.EmpsID = e.EmpsID AND APPROVED = 'N' AND l.EmpsID = 'AGIUCL01'

AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'

union

(SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS APPROVED' FROM labors l, emps e

WHERE l.EmpsID = e.EmpsID AND APPROVED = 'A' AND l.EmpsID = 'AGIUCL01'

AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05')

union

SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS REJECTED' FROM labors l,empse

WHERE l.EmpsID = e.EmpsID AND APPROVED = 'R'

AND l.EmpsID = 'AGIUCL01'

AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'

Query:

`SELECT e.ENAME 'NAME',`

SUM(CASE WHEN l.APPROVED='N' THEN l.HOURS ELSE 0 END) 'HOURS ENTERED',

SUM(CASE WHEN l.APPROVED='A' THEN l.HOURS ELSE 0 END) 'HOURS APPROVED',

SUM(CASE WHEN l.APPROVED='R' THEN l.HOURS ELSE 0 END) 'HOURS REJECTED'

FROM labors l ,emps e

where l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05' and

e.TERM_DATE < e.REHIRE_DATE

or e.TERM_DATE IS NULL

group by (e.empsid)

I am executing the query my result from the table is this.

`NAME HOURS ENTERED HOURS APPROVED HOURS REJECTED`

CLAUDINEAGIUS 292.00 39.00 20.00

TODD MICHAEL ALDAMA 292.00 39.00 20.00

STEVE ALMGREN 292.00 39.00 20.00

SHAWN ANDERSON 292.00 39.00 20.00

JARETT BARNETT 292.00 39.00 20.00

But I am not getting the every user entered the current time lines.

Answer Source

You can use conditionals in the `SUM`

:

```
SELECT e.ENAME 'NAME',
SUM(CASE WHEN APPROVED='N' THEN l.HOURS ELSE 0 END) 'HOURS ENTERED',
SUM(CASE WHEN APPROVED='A' THEN l.HOURS ELSE 0 END) 'HOURS APPROVED',
SUM(CASE WHEN APPROVED='R' THEN l.HOURS ELSE 0 END) 'HOURS REJECTED'
FROM labors l, emps e
WHERE l.EmpsID = e.EmpsID
AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'
```