Consider the below queries

Query1
---------
Select
Col1,Col2,Col3,
Count(Col4),
Sum(Col5)
From Table
Group By Col1,Col2,Col3

Query2
----------
Select
Col1,Col2,Col3,
COUNT(Col4) OVER (PARTITION BY <Your partition by Columns> ORDER BY (Select 1)),
SUM(Col5) OVER (PARTITION BY <Your partition by Columns> ORDER BY (Select 1))
From Table

Which is better and why?

 Posted by Rajnilari2015 on 3/16/2016 | Category: Sql Server Interview questions | Views: 1718 | Points: 40
Answer:

The second on e is better.The disadvantage of using the first approach is that we need to group by all other non-aggregate columns which some times is not desirable and yields wrong results/not desired ones. The second approach is better as we have the choice of grouping the columns by our own choice and at a case by case basis.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response