1
Reply

Group by not working for subquery please help

Ankit Kumar

Ankit Kumar

Dec 26 2016 4:14 AM
229
select
M.vch_dmo_name,
count(1) as total,
DATENAME(MM,DTM_REQ_ON) MONTH,
(select count(1) from M_PMS_FormA A where int_status_id=5 and A.int_dmo_id=M.int_dmo_id ) Approved,
(select count(1) from M_PMS_FormA A where int_status_id=3 and A.int_dmo_id=M.int_dmo_id ) Rejected,
(select count(1) from M_PMS_FormA A where int_status_id not in (5,3) and A.int_dmo_id=M.int_dmo_id ) [Progress]
from M_Mining_dmo M inner join M_PMS_FormA A on M.int_dmo_id=A.int_dmo_id group by DATEPART(MM,DTM_REQ_ON),DATENAME(MM,DTM_REQ_ON),vch_dmo_name,M.int_dmo_id

Answers (1)