praveen1981
asked on
Sql query
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.
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.
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
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
ASKER
Hi
I want the result as follows
EmpId Inprogress Completed NotOpen
001 2 1 0
002 0 0 1
could you please suggest.
I want the result as follows
EmpId Inprogress Completed NotOpen
001 2 1 0
002 0 0 1
could you please suggest.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Thanks
Any particular reason for the grade b?? Because i'm assuming your problem was solved on this one properly..
Open in new window
Saurabh...