Need Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Query help

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-01-30 : 03:18:07

I have 2 tables as follow

User

Id Name
1 abc
1 xyz
1 pqr
2 wxy
3 def

LID_status

ID LID Status Date ModifiedBy
1 1 open 1/1/2009 abc
2 1 submit 1/2/2009 xyz
3 1 closed 1/3/2009 pqr
4 2 open 1/1/2009 abc
5 2 submit 1/2/2009 xyz
6 2 submit 1/3/2009 xyz
7 2 progess 1/4/2009 def
8 3 open 1/5/2009 abc
9 3 submit 1/6/2009 xyz
10 3 closed 1/7/2009 pqr

I want the count of LID for each status whose ID = 1 in User Table

o/p should be as follow

count_LID Status
3 open
3 submit
2 closed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:30:54

[code]SELECT l.Status,COUNT(LID)
FROm Users u
INNER JOIN LID_Status l
ON l.Modified_By=u.Name
WHERE u.ID=1
GROUP BY l.Status[/code]