SQL Server: How can I use the COUNT clause without GROUPing?
Solution 1
Assuming you are using a newish version of SQL Server (2008+ from memory) then you can use analytic functions.
Simplifying things somewhat, they are a way of way of doing an aggregate over a set of data instead of a group - an extension on basic aggregates.
Instead of this:
SELECT ... , COUNT(*) as MatchCount FROM Table WHERE ...
You do this:
SELECT ... , COUNT(*) as MatchCount OVER (PARTITION BY <group fields> ORDER BY <order fields> ) FROM Table WHERE ... GROUP BY
Without actually running some code, I can't recall exactly which aggregates that you can't use in this fashion. Count is fine though.
Solution 2
Well, you can use OVER clause, which is an window function.
SELECT TOP (1)
OrderID, CustID, EmpID,
COUNT(*) OVER() AS MatchCount
FROM Sales.Orders
WHERE OrderID % 2 = 1
ORDER BY OrderID DESC
Solution 3
Try next query:
select top 1
*, count(*) over () rowsCount
from
(
select
*, dense_rank() over (order by ValueForOrder) n
from
myTable
) t
where
n = 1
Ehryk
I'm a Computer (Web) Programmer/Analyst based in Anchorage, AK and Minneapolis, MN. I use (among other things) ASP.NET, C# and SQL Server. I build things. Bicycles, computers, websites, guitars, cars, motorcycles, sound sytems... lots of things. Resume: http://ericmenze.com Personal Website: http://ehryk.com Pause your videos at specific locations: http://pauseforlater.com Calculate and build spoked bicycle wheels: http://wheelspoking.com See activity specific analysis of your GPX Files: http://gpxdataanalyzer.com Tool to open command/powershell prompts from any location (Windows): https://github.com/Ehryk/ContextMenuTools
Updated on July 10, 2022Comments
-
Ehryk almost 2 years
I'm looking get two things from a query, given a set of contraints:
- The first match
- The total number of matches
I can get the first match by:
SELECT TOP 1 ID, ReportYear, Name, SignDate, ... FROM Table WHERE ... ORDER BY ... //I can put in here which one I want to take
And then I can get the match count if I use
SELECT MIN(ID), MIN(ReportYear), MIN(Name), MIN(SignDate), ... , COUNT(*) as MatchCount FROM Table WHERE ... GROUP BY ??? // I don't really want any grouping
I really want to avoid both grouping and using an aggregate function on all my results. This question SQL Server Select COUNT without using aggregate function or group by suggests the answer would be
SELECT TOP 1 ID, ReportYear, Name, SignDate, ... , @@ROWCOUNT as MatchCount FROM Table
This works without the TOP 1, but when it's in there, @@ROWCOUNT = number of rows returned, which is 1. How can I get essentially the output of COUNT(*) (whatever's left after the where clause) without any grouping or need to aggregate all the columns?
What I don't want to do is repeat each of these twice, once for the first row and then again for the @@ROWCOUNT. I'm not finding a way I can properly use GROUP BY, because I strictly want the number of items that match my criteria, and I want columns that if I GROUPed them would throw this number off - unless I'm misunderstanding GROUP BY.