Is mandatory to place aggregate function when group by to use in sql server [Resolved]

Posted by Jitendrasoft09 under Sql Server on 6/26/2013 | Points: 10 | Views : 1103 | Status : [Member] [MVP] | Replies : 4
Hi All,
I want to understand Is mandatory to place aggregate function when group by to use in sql server, let me know if anybody knows.

Jitendra Kumar
If my post helps you, plz mark as an answer.



Responses

Posted by: Niladri.Biswas on: 6/26/2013 [Member] Platinum | Points: 50

Up
0
Down

Resolved
Select Country,State 

from @t
group by Country,State


I have not applied any aggregate function. It will get you all unique pairs listed in group by .

Best Regards,
Niladri Biswas

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 6/26/2013 [Member] Platinum | Points: 25

Up
0
Down
The answer is "No". SELECT with GROUP BY can be used as an alternative to SELECT DISTINCT

e.g.
Declare @t table(FruitName varchar(50))

Insert into @t values('Apple'),('Apple'),('Orange'),('Banana'),('Mango'),('Mango')

Select FruitName
From @t
Group By FruitName

/* Result */
FruitName

-------------
Apple
Banana
Mango
Orange


Best Regards,
Niladri Biswas

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 6/26/2013 [Member] Platinum | Points: 25

Up
0
Down
Declare @t table(FruitName varchar(50))

Insert into @t values('Apple'),('Apple'),('Orange'),('Banana'),('Mango'),('Mango')

SELECT FruitName, ROW_NUMBER() OVER (ORDER BY FruitName) FROM @t GROUP BY FruitName

SELECT DISTINCT FruitName, ROW_NUMBER() OVER (ORDER BY FruitName) FROM @t


Both statements are meant to return distinct items ranked. However, only the first statement (with GROUP BY) returns the results as expected, while the latter (with DISTINCT) returns all items. The reason is, the SELECT clause in SQL Server is evaluated after GROUP BY, but before DISTINCT, which makes you prefer GROUP BY over DISTINCT in this case.

Best Regards,
Niladri Biswas

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jitendrasoft09 on: 6/26/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
Nicely Explained with code, Thanks Niladri....

Jitendra Kumar
If my post helps you, plz mark as an answer.

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response