December 19, 2011 at 8:19 am
I have this query:
SELECT COUNT(*)
FROM Departments INNER JOIN
Employees ON Departments.Id = Employees.DepartmentId INNER JOIN
Results ON Employees.Id = Evaluation.EmployeeId
WHERE (Results.Year = 2011)
and Employees.OrgId = 73
GROUP BY Employees.DepartmentId
that produces 3 records as follows:
2
1
20
I need a row count of this rather than a row count of department Ids.
In this case the result should be only one record with the result as "3"
If I select @@ROWCOUNT instead of COUNT(*), I get 3 records with the result of "3" each.
The result should be "3" but it should only produce 1 record.
How can I do this?
Thanks for your help.
December 19, 2011 at 9:18 am
Any help will be appreciated.
Thanks.
December 19, 2011 at 9:46 am
Hi Ram
I'm not in a position to try this at the moment but you could try making your query a sub-query as follows
select count(*) from (your query here)
Hope this helps
Dave
December 19, 2011 at 9:58 am
I'd use David's technique for a quick count.
Is this something that runs often, or just an ad hoc type report? If the latter, then select count(*) from (subquery) makes sense.
December 19, 2011 at 9:58 am
select count(*) from
(
SELECT COUNT(*) as myCount
FROM Departments INNER JOIN
Employees ON Departments.Id = Employees.DepartmentId INNER JOIN
Results ON Employees.Id = Evaluation.EmployeeId
WHERE (Results.Year = 2011)
and Employees.OrgId = 73
GROUP BY Employees.DepartmentId
) x
Notice that for this to work you will have to assign the count aggregate an alias in your original query. You also have to assign the subquery an alias.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 19, 2011 at 10:00 am
If I'm reading your post right, you need to know how many DepartmentID's you have for the given filter criteria? Would a distinct count of DepartmentID's work?
SELECT COUNT(DISTINCT DepartmenID)
FROM tablenames
WHERE Year = 2011 and orgid = 73
December 19, 2011 at 1:04 pm
DISTINCT cannot work in this case but wrapping the query in select clause does work. Thanks to Sean and David.
Thanks again.
December 19, 2011 at 2:00 pm
ramadesai108 (12/19/2011)
I have this query:SELECT COUNT(*)
FROM Departments INNER JOIN
Employees ON Departments.Id = Employees.DepartmentId INNER JOIN
Results ON Employees.Id = Evaluation.EmployeeId
WHERE (Results.Year = 2011)
and Employees.OrgId = 73
GROUP BY Employees.DepartmentId
that produces 3 records as follows:
2
1
20
I need a row count of this rather than a row count of department Ids.
In this case the result should be only one record with the result as "3"
If I select @@ROWCOUNT instead of COUNT(*), I get 3 records with the result of "3" each.
The result should be "3" but it should only produce 1 record.
How can I do this?
Thanks for your help.
None of the solutions quoted so far will work properly, if at all, because table [Evaluation] doesn't appear anywhere as a table source 😉
SELECT DepartmentIds = COUNT(*)
FROM (
SELECT e.DepartmentId
FROM Departments d
INNER JOIN Employees e ON d.Id = e.DepartmentId
INNER JOIN Results r ON e.Id = r.EmployeeId
WHERE (r.[Year] = 2011)
and e.OrgId = 73
GROUP BY e.DepartmentId
) d
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 2:19 pm
I read it that you are saying
your current script returns
2
10
19
However, what you want like for example is 1 row to be returned that would sum the above results so the reslult would be
31
SO to return that just drop the group by clause on your query
SELECT COUNT(*)
FROM Departments INNER JOIN
Employees ON Departments.Id = Employees.DepartmentId INNER JOIN
Results ON Employees.Id = Evaluation.EmployeeId
WHERE (Results.Year = 2011)
and Employees.OrgId = 73
Alternativly and im not sure what you would want to know this as it wouldnt really tell you much as previously stated just sub query your query but remove the count (*) from subquery
select count(*) from
(
SELECT Employees.DepartmentId
FROM Departments INNER JOIN
Employees ON Departments.Id = Employees.DepartmentId INNER JOIN
Results ON Employees.Id = Evaluation.EmployeeId
WHERE (Results.Year = 2011)
and Employees.OrgId = 73
GROUP BY Employees.DepartmentId
) X
***The first step is always the hardest *******
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply