How to perform a count on a set of results

I am trying to perform a count on a set of values but the count is bringing in extra values.

Here is some background information. The following T-SQL query returns the data I need:

SELECT DISTINCT [REQ_INFO].UDAValue as 'UDA value', REQ_INFO.ProjectID, 
REQ_INFO.BaselineID, Req_ID
FROM [REQ_INFO]
INNER JOIN [Project_INFO] ON [REQ_INFO].[ProjectID]=[Project_INFO].[ProjectID]
INNER JOIN [Baseline_INFO]ON [REQ_INFO].[BaselineID]=[Baseline_INFO].[BaselineID]
WHERE [Project_INFO].[Name] = 'Address Book'
AND [Baseline_INFO].Name = 'Current Baseline'
AND [UDAName] = 'Requirement Priority'
ORDER BY REQ_INFO.Req_ID

This returns the following data:

How I also need to count how many times each value occurs in the UDA value column. The total sum should be 11 as shown above. However I tried the following code but it is counting extra rows:

SELECT [REQ_INFO].UDAValue as 'UDA value',COUNT (*) as 'UDA Num'
FROM [REQ_INFO]
INNER JOIN [Project_INFO] ON [REQ_INFO].[ProjectID]=[Project_INFO].[ProjectID] 
INNER JOIN [Baseline_INFO]ON [REQ_INFO].[BaselineID]=[Baseline_INFO].[BaselineID]
WHERE [Project_INFO].[Name] = 'Address Book' 
AND [Baseline_INFO].Name = 'Current Baseline'
AND [UDAName] = 'Requirement Priority'
GROUP BY REQ_INFO.UDAValue

Does anyone have suggestions on what I am doing wrong?

Answers


Looks to me like it's most likely the distinct statement from the first query that is causing the difference.

If what you are looking for is the actual counts of the occurances of [REQ_INFO].UDAValue. Then you should remove the distinct clause from the first query to see what the second query is actually counting.

As an alternative you can see the total count of the grouped values of the first query like this, but that is different then both your first and second queries.

SELECT [REQ_INFO].UDAValue as 'UDA value',COUNT (*) as 'UDA Num' 
FROM [REQ_INFO] 
INNER JOIN [Project_INFO] ON [REQ_INFO].[ProjectID]=[Project_INFO].[ProjectID]  
INNER JOIN [Baseline_INFO]ON [REQ_INFO].[BaselineID]=[Baseline_INFO].[BaselineID] 
WHERE [Project_INFO].[Name] = 'Address Book'  
AND [Baseline_INFO].Name = 'Current Baseline' 
AND [UDAName] = 'Requirement Priority' 
GROUP BY REQ_INFO.UDAValue, REQ_INFO.ProjectID, REQ_INFO.BaselineID, Req_ID

It's hard to tell from your question if that's what you want either. If you truly want a count of the values from the first query as they appear (distinct statement affecting the 4 columns and all). Then you'd probably approach it with a subquery.


Change your count to include a distinct; something like this:

SELECT [REQ_INFO].UDAValue as 'UDA value',COUNT (DISTINCT REQ_INFO.Req_ID) as 'UDA Num'
... rest of your query

I was able to get round the problem by creating a temporary table to allow me to perform the count. This is the SQL statement that I used:

DECLARE @UDAcount TABLE (UDAValue nvarchar(max), ProjectID int, 
BaselineID int, Req_ID int, Version nvarchar(128))

INSERT INTO @UDAcount (UDAValue, ProjectID, BaselineID, Req_ID, Version)
SELECT DISTINCT [REQ_INFO].UDAValue as 'UDA value', REQ_INFO.ProjectID, 
REQ_INFO.BaselineID, Req_ID, REQ_INFO.Version
FROM [REQ_INFO]
INNER JOIN [Project_INFO] ON [REQ_INFO].[ProjectID]=[Project_INFO].[ProjectID]
INNER JOIN [Baseline_INFO]ON [REQ_INFO].[BaselineID]=[Baseline_INFO].[BaselineID]
WHERE [Project_INFO].[Name] = 'Address Book'
AND [Baseline_INFO].Name = 'Current Baseline'
AND [UDAName] = 'Requirement Priority'
ORDER BY REQ_INFO.Req_ID

SELECT UDAValue as 'UDA value',COUNT (*) as 'UDA Num'
FROM @UDAcount
GROUP BY UDAValue

It may not be the most elegant solution but it does return the desired results. If you have any suggestions on how to improve on this I'd be glad to hear them.


Need Your Help

Find cycle which contains two vertices in undirected graph

algorithm graph

I need to find shortest non-recursive cycle from first vertex to second vertex in undirected graph.

How to localize numbers for iPhone app?

iphone localization

In my iPhone app, I need to display object counts which I then localize, since English makes the distinction of singular and plural, I do the following