CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
SQLSam
SQLSam

Reputation: 537

SQL Row_Count function with Partition

I have a query that returns a set of results as a table called DATA, from several UNION ALL joined queries.

I am then doing ROW_NUMBER() on this, to get the row number for a specific grouping (WorksOrderNo)

ROW_NUMBER() Over(partition by Data.WorksOrderNo order by Data.WorksOrderNo) as RowNo,

Is there an equivalent ROW_Count function where I can specify a partition, and return the count of rows for that partition?

ROW_Count() Over(partition by Data.WorksOrderNo order by Data.WorksOrderNo) as RowNo ???

Reason being, this is query being used to drive a report layout. As part of this, I need to format based on whether the total row count for each WorksOrderNo is >1 or not.

So for instance if there were three rows for a works order, the row_number function currently returns 1, 2 and 3, where the row count would return 3 on each row.

Upvotes: 1

Views: 8008

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The function is simply COUNT(). In SQL Server, all the aggregation functions can be used as window functions, as long as they do not use DISTINCT.

Note that for the total count, you do not want the ORDER BY:

COUNT(*) Over (partition by Data.WorksOrderNo) as cnt

If you include the ORDER BY, then the COUNT() is cumulative, rather than constant for all rows in the partition.

Upvotes: 7

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

It looks like you just need group by and count:

select WorksOrderNo, count(*) as Row_Count
from Data 
group by WorksOrderNo

Upvotes: 1

Related Questions