Count with a Union
-
I have this query
SELECT cs.Name, COUNT(req.RequestId) AS Total, imp.UserId
FROM dbo.tblRequest req INNER JOIN
dbo.tblImplementer imp ON req.RequestId = imp.RequestId RIGHT OUTER JOIN
dbo.tblChangeStatus cs ON req.ChangeStatusId = cs.ChangeStatusId
GROUP BY cs.Name, imp.UserId
HAVING (imp.UserId = 195)
it returns
Completed 1 195
Open 2 195
I want it to return
Completed 1 195
Open 2 195
-
This query returns the data that i need, but does not have the Id filter from tblImplementer.
SELECT cs.Name, COUNT(req.RequestId) AS Total
FROM dbo.tblRequest req RIGHT OUTER JOIN
dbo.tblChangeStatus cs ON req.ChangeStatusId = cs.ChangeStatusId
GROUP BY cs.Name
-
I cannot see what your question is. What is returns and what you want look the same to me...
I wasn't born stupid - I had to study.
-
On...Let me explain better. The UserId = 195 has 3 records (1 Completed and 2 Open) in the tblRequest table (req). When I run this query below, I get a count of all records in the tblRequest table.
SELECT cs.Name, COUNT(req.RequestId) AS Total
FROM dbo.tblRequest req RIGHT OUTER JOIN
dbo.tblChangeStatus cs ON req.ChangeStatusId = cs.ChangeStatusId
GROUP BY cs.Name
DATA RETURNED
Canceled 0
Completed 2
Hold 0
Open 6
Pending 0
Scheduled 0
The data that I am looking for SHOULD be
Canceled 0
Completed 1
Hold 0
Open 2
Pending 0
Scheduled 0
I have to join the Query on the table tblImplementer
tblImplementer
UserId int
RequestId int
UserRoleId int
tblChangeStatus
ChangeStatusId
ChangeStatusName
Data Example of tblChangeStatus
1 Canceled
2 Completed
3 Hold
4 Open
5 Pending
6 Scheduled
When I run the query below:
SELECT cs.Name, COUNT(req.RequestId) AS Total, dbo.tblImplementer.UserId
FROM dbo.tblRequest req INNER JOIN
dbo.tblImplementer ON req.RequestId = dbo.tblImplementer.RequestId RIGHT OUTER JOIN
dbo.tblChangeStatus cs ON req.ChangeStatusId = cs.ChangeStatusId
GROUP BY cs.Name, dbo.tblImplementer.UserId
HAVING (dbo.tblImplementer.UserId = 195)
I get the following records:
Completed 1
Open 2
BUT I need to return
Canceled 0
Completed 1
Hold 0
Open 2
Pending 0
Scheduled 0
.....as mentioned above
-
tblRequest
RequestId
ChangeStatusId
....
....
-
I got it SubSelect
SELECT cs.Name, COUNT(req.RequestId) AS Total
FROM dbo.tblRequest req RIGHT OUTER JOIN (SELECT * FROM tblImplementer WHERE UserID = @ImplementerId) AS
tblImplementer ON req.RequestId = tblImplementer.RequestId RIGHT OUTER JOIN
dbo.tblChangeStatus cs ON req.ChangeStatusId = cs.ChangeStatusId
GROUP BY cs.Name
-
You 'da woman! Sorry, I got caught up at work and could not come back to this..., glad you were able to figure it out.
(It helps to give tables structures and some data for the next question you may ask...)
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply