We help IT Professionals succeed at work.

Sql query

praveen1981
praveen1981 asked
on
Hi,
Here are the three table TableA,TableB, and TableC with following columns.
Table A
EmpId  Name
001        abc
002        bbc
Table B
Id  empid , date, statusId  (This is reference of Statusid from table C)
1    001     ,12-2-2015,   1
2    001  , 13-2-2015,     1
3    001  , 14-2-2015,     2
4    002  , 15-2-2015 ,     3
Table C
statusId  statusName
1      Inprogress
2      Completed
3      Not Open


Now what is the count of inprogress and what is the count of completed and what is the count of Not open for each employee.
Please provide the query.
Comment
Watch Question

Top Expert 2015

Commented:
You can use the following query...

select tb1.name,tb3.statusname,count(tb2.id)
from table1 tb1
left join table2 tb2 on tb1.empid=tb2.empid
left join table tb3 on tb2.statusid=tb3.statusid

Open in new window


Saurabh...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
SELECT A.Name C.StatusName, COUNT(B.StatusID)
FROM TableB B
    INNER JOIN TableC C ON B.StatusID = C.StatusID
    INNER JOIN TableA  A ON B.Empid = A.EmpId
GROUP BY A.Name, C.StatusName

Open in new window

Top Expert 2015

Commented:
Ahh Good catch vitor..I missed group by in my statement my bad...

select tb1.name,tb3.statusname,count(tb2.id)
from table1 tb1
left join table2 tb2 on tb1.empid=tb2.empid
left join table tb3 on tb2.statusid=tb3.statusid
group by tb1.name,tb3.statusname

Open in new window

Author

Commented:
Hi

I want the result as follows

EmpId  Inprogress Completed NotOpen

001           2                    1                0
002           0                    0                1

could you please suggest.
Top Expert 2015
Commented:
You can do something like this to do what you are looking for..

select tb1.empid,
'Inprogress'=sum(case tb3.statusname='Inprogress' then 1 else 0 end),
'Completed'=sum(case tb3.statusname='Completed' then 1 else 0 end),
'Not Open'=sum(case tb3.statusname='Not Open' then 1 else 0 end)
from tablea tb1
left join tableb tb2 on tb1.empid=tb2.empid
left join tablc tb3 on tb2.statusid=tb3.statusid
group by tb1.empid

Open in new window


Saurabh...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You can use PIVOT table:
SELECT *
FROM (SELECT A.EmpId, C.StatusName, B.StatusID
		FROM TableB B
			INNER JOIN TableC C ON B.StatusID = C.StatusID
			INNER JOIN TableA  A ON B.Empid = A.EmpId) T
PIVOT
	(COUNT(StatusID) FOR StatusName IN (Inprogress, Completed, [Not Open])
) AS PivotTable

Open in new window

Author

Commented:
Thanks
Top Expert 2015

Commented:
Any particular reason for the grade b?? Because i'm assuming your problem was solved on this one properly..