CODEWITHSUNDEEP
sql-servergroup-byaggregate-functions
Onlydocs
Onlydocs

Reputation: 97

Aggregate WITHOUT Group By returning duplicates

I have the following data.

Property, Value
Odd,1
Even,2
Odd,3
Even,4
Odd,5
Even,6

With GROUP BY

SELECT Property, COUNT(*) FROM Tab1 GROUP BY Property

Result:

Property, COUNT(*)
Odd 3
Even 3

But when I am using the aggregate function it is returning duplicate values.

SELECT property,count(*) over (partition by property) AS count
FROM Tab1;

Result

Property, count
Even 3
Even 3
Even 3
Odd 3
Odd 3
Odd 3

I want the same result as I get using GROUP BY.

SELECT property, count FROM (
SELECT property,count(*) over (partition by property) AS count
FROM Tab1) AS tab2;

Is there any other method than this?

Upvotes: 1

Views: 733

Answers (1)

Nick
Nick

Reputation: 147166

That's how window functions work; you get a row returned for every row in the source table, not an aggregated row as when you use GROUP BY. You can achieve the same result using SELECT DISTINCT:

SELECT DISTINCT property,
       COUNT(*) OVER (PARTITION BY property) AS count
FROM Tab1

Demo on dbfiddle

Upvotes: 1

Related Questions