question

jhowe avatar image
jhowe asked

count based on result of row

hi all, i'm trying to figure out how to do a count based on the results of a query so please consider the following code below. I'm trying to get an accepted count for the request number on that row, not a total. I'm not sure how to do this! Please excuse the formatting i'm not sure how to format in this. Some pointers for that would be appreciated as well! SELECT *, (SELECT COUNT(*) FROM dbo.Request JOIN dbo.AllocationAudit ON dbo.Request.RequestID = dbo.AllocationAudit.RequestID WHERE AllocationStatus = 'Accepted') AS AcceptedCount FROM dbo.Request RequestID RequestNumber RequestType Request UserID AcceptedCount ---------------------------------------- 1 S27784 Hotel El Salvador 1 15 2 S95068 Rail Canada 5 15 3 S38668 Rail Nauru 3 15 4 S77434 Hotel Saint Lucia 4 15 5 S61653 Flight Tajikistan 2 15 USE [master] GO /****** Object: Database [test] Script Date: 25/02/2013 12:14:28 ******/ CREATE DATABASE [test] USE [test] GO CREATE TABLE [dbo].[User]( [UserID] [int] NULL, [Firstname] [nvarchar](50) NULL, [Lastname] [nvarchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Request]( [RequestID] [int] NULL, [RequestNumber] [nvarchar](50) NULL, [RequestType] [nvarchar](50) NULL, [Request] [nvarchar](max) NULL, [UserID] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[AllocationAudit]( [AllocationID] [int] NULL, [UserID] [int] NULL, [RequestID] [int] NULL, [RequestNumber] [nvarchar](50) NULL, [AllocationStatus] [nvarchar](50) NULL, [AllocatedUser] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[User]([UserID], [Firstname], [Lastname]) SELECT 1, N'Ginger', N'Huang' UNION ALL SELECT 2, N'Jami', N'Watts' UNION ALL SELECT 3, N'Erika', N'English' UNION ALL SELECT 4, N'Jeffrey', N'Cole' UNION ALL SELECT 5, N'Tania', N'Hardin' GO INSERT INTO [dbo].[Request]([RequestID], [RequestNumber], [RequestType], [Request], [UserID]) SELECT 1, N'S27784', N'Hotel', N'El Salvador', 1 UNION ALL SELECT 2, N'S95068', N'Rail', N'Canada', 5 UNION ALL SELECT 3, N'S38668', N'Rail', N'Nauru', 3 UNION ALL SELECT 4, N'S77434', N'Hotel', N'Saint Lucia', 4 UNION ALL SELECT 5, N'S61653', N'Flight', N'Tajikistan', 2 UNION ALL SELECT 6, N'S94489', N'Holiday', N'Aruba', 5 UNION ALL SELECT 7, N'S87146', N'Hotel', N'Austria', 1 UNION ALL SELECT 8, N'S28389', N'Holiday', N'North Korea', 4 UNION ALL SELECT 9, N'S34345', N'Rail', N'Antarctica', 3 UNION ALL SELECT 10, N'S53747', N'Hotel', N'Western Sahara', 2 UNION ALL SELECT 11, N'S53901', N'Holiday', N'Tunisia', 3 UNION ALL SELECT 12, N'S20471', N'Flight', N'Eritrea', 4 UNION ALL SELECT 13, N'S00401', N'Rail', N'New Caledonia', 2 UNION ALL SELECT 14, N'S20186', N'Flight', N'Samoa', 5 UNION ALL SELECT 15, N'S45367', N'Holiday', N'Suriname', 4 UNION ALL SELECT 16, N'S30578', N'Rail', N'Iraq', 5 UNION ALL SELECT 17, N'S51295', N'Holiday', N'Mayotte', 4 UNION ALL SELECT 18, N'S03694', N'Holiday', N'Qatar', 3 UNION ALL SELECT 19, N'S19892', N'Flight', N'Latvia', 2 UNION ALL SELECT 20, N'S17612', N'Hotel', N'Vietnam', 2 INSERT INTO [dbo].[AllocationAudit]([AllocationID], [UserID], [RequestID], [RequestNumber], [AllocationStatus], [AllocatedUser]) SELECT 1, 4, 2, N'S77434', N'Completed', N'Jeffrey Cole' UNION ALL SELECT 2, 4, 4, N'S51295', N'Allocated', N'Tania Hardin' UNION ALL SELECT 3, 4, 2, N'S27784', N'Allocated', N'Erika English' UNION ALL SELECT 4, 3, 18, N'S53901', N'Completed', N'Tania Hardin' UNION ALL SELECT 5, 3, 9, N'S17612', N'Allocated', N'Jami Watts' UNION ALL SELECT 6, 3, 12, N'S94489', N'Allocated', N'Erika English' UNION ALL SELECT 7, 1, 12, N'S17612', N'Accepted', N'Jeffrey Cole' UNION ALL SELECT 8, 1, 16, N'S03694', N'Completed', N'Tania Hardin' UNION ALL SELECT 9, 5, 13, N'S51295', N'Accepted', N'Tania Hardin' UNION ALL SELECT 10, 4, 3, N'S20471', N'Completed', N'Jeffrey Cole' UNION ALL SELECT 11, 2, 17, N'S20186', N'Allocated', N'Tania Hardin' UNION ALL SELECT 12, 2, 20, N'S27784', N'Accepted', N'Tania Hardin' UNION ALL SELECT 13, 4, 2, N'S34345', N'Allocated', N'Jeffrey Cole' UNION ALL SELECT 14, 1, 5, N'S61653', N'Accepted', N'Tania Hardin' UNION ALL SELECT 15, 2, 12, N'S20471', N'Completed', N'Jami Watts' UNION ALL SELECT 16, 1, 17, N'S95068', N'Completed', N'Erika English' UNION ALL SELECT 17, 4, 13, N'S00401', N'Accepted', N'Erika English' UNION ALL SELECT 18, 1, 16, N'S30578', N'Allocated', N'Erika English' UNION ALL SELECT 19, 3, 2, N'S34345', N'Accepted', N'Ginger Huang' UNION ALL SELECT 20, 1, 5, N'S20471', N'Allocated', N'Tania Hardin' UNION ALL SELECT 21, 4, 15, N'S28389', N'Completed', N'Erika English' UNION ALL SELECT 22, 5, 9, N'S45367', N'Completed', N'Jami Watts' UNION ALL SELECT 23, 5, 13, N'S95068', N'Accepted', N'Jeffrey Cole' UNION ALL SELECT 24, 3, 15, N'S77434', N'Allocated', N'Jeffrey Cole' UNION ALL SELECT 25, 5, 16, N'S20186', N'Accepted', N'Tania Hardin' UNION ALL SELECT 26, 3, 18, N'S95068', N'Allocated', N'Erika English' UNION ALL SELECT 27, 1, 17, N'S51295', N'Completed', N'Tania Hardin' UNION ALL SELECT 28, 1, 17, N'S51295', N'Allocated', N'Jami Watts' UNION ALL SELECT 29, 2, 14, N'S53747', N'Completed', N'Jami Watts' UNION ALL SELECT 30, 5, 4, N'S53747', N'Accepted', N'Jami Watts' UNION ALL SELECT 31, 3, 11, N'S17612', N'Allocated', N'Jeffrey Cole' UNION ALL SELECT 32, 2, 11, N'S45367', N'Allocated', N'Jami Watts' UNION ALL SELECT 33, 1, 15, N'S51295', N'Accepted', N'Erika English' UNION ALL SELECT 34, 4, 9, N'S20186', N'Allocated', N'Jami Watts' UNION ALL SELECT 35, 4, 17, N'S94489', N'Completed', N'Jeffrey Cole' UNION ALL SELECT 36, 4, 19, N'S95068', N'Completed', N'Erika English' UNION ALL SELECT 37, 1, 11, N'S19892', N'Allocated', N'Tania Hardin' UNION ALL SELECT 38, 5, 5, N'S87146', N'Accepted', N'Erika English' UNION ALL SELECT 39, 4, 16, N'S87146', N'Accepted', N'Jeffrey Cole' UNION ALL SELECT 40, 3, 20, N'S28389', N'Accepted', N'Jeffrey Cole' UNION ALL SELECT 41, 4, 16, N'S77434', N'Allocated', N'Jami Watts' UNION ALL SELECT 42, 3, 2, N'S95068', N'Allocated', N'Erika English' UNION ALL SELECT 43, 1, 10, N'S95068', N'Accepted', N'Ginger Huang' UNION ALL SELECT 44, 2, 12, N'S61653', N'Completed', N'Erika English' UNION ALL SELECT 45, 3, 6, N'S51295', N'Completed', N'Jeffrey Cole' UNION ALL SELECT 46, 1, 19, N'S17612', N'Allocated', N'Jeffrey Cole' UNION ALL SELECT 47, 1, 1, N'S27784', N'Accepted', N'Erika English' UNION ALL SELECT 48, 2, 5, N'S27784', N'Allocated', N'Ginger Huang' UNION ALL SELECT 49, 3, 20, N'S03694', N'Completed', N'Jeffrey Cole' UNION ALL SELECT 50, 1, 19, N'S20471', N'Completed', N'Jami Watts' GO
sql-server-2008sql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Here's one way: select dbo.Request.RequestId, dbo.Request.RequestNumber, dbo.Request.RequestType, dbo.Request.Request, dbo.Request.UserID, sum(case when AllocationStatus = 'Accepted' then 1 else 0 end) as AcceptedCount from dbo.Request join dbo.AllocationAudit on dbo.Request.RequestID = dbo.AllocationAudit.RequestID group by dbo.Request.RequestId, dbo.Request.RequestNumber, dbo.Request.RequestType, dbo.Request.Request, dbo.Request.UserID
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Change the join condition to from dbo.Request join dbo.AllocationAudit on dbo.Request.RequestNumber = dbo.AllocationAudit.RequestNumber
2 Likes 2 ·
hi kev, no this doesn't seem to give me the expected results. if you run your query and look at the first row it has acceptedcount of 1. when you run SELECT * FROM dbo.AllocationAudit WHERE RequestNumber = 'S27784' there are two accepted requests in dbo.AllocationAudit
0 Likes 0 ·
Yes that's better! in the time you spotted that join i came up with a different way of doing it, which works with my original query (as i have other where clauses etc.) let me know what you think... this was my original idea of joining on to a count.
0 Likes 0 ·
jhowe avatar image
jhowe answered
select R.RequestId, R.RequestNumber, R.RequestType, R.Request, R.UserID, AcceptedCount.AcceptedCount from dbo.Request R JOIN (SELECT COUNT(RequestID) AS AcceptedCount, RequestID FROM dbo.AllocationAudit WHERE AllocationStatus = 'Accepted' GROUP BY RequestID) AS AcceptedCount ON R.RequestID = AcceptedCount.RequestID group by R.RequestId, R.RequestNumber, R.RequestType, R.Request, R.UserID, AcceptedCount.AcceptedCount
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.