Group by

Question

Post reply

Group by

arup chakraborty

Hall of Fame

Points: 3640
More actions
August 25, 2009 at 4:54 am

#209911

Hi,
SELECT a,b FROM #temp GROUP BY a,b ORDER BY a,b
Suppose I have 100 columns in my table and I need to GROUP BY those columns by 50 columns.Is there any other alternatives other than including those 50 columns in SELECT clause?
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11381 More actions · Answer 1

you can group by fields that are not in the select clause. Results won't make much sense, but sql allows this.

SELECT a,b

FROM #temp

GROUP BY a,b,c,d

ORDER BY a,b,c,d

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Thanks Martin.

But is it possible to provide column names in SELECT clause which are not in GROUP BY clause?

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 3

arup_kc (8/25/2009)
Hi,
SELECT a,b FROM #temp GROUP BY a,b ORDER BY a,b
Suppose I have 100 columns in my table and I need to GROUP BY those columns by 50 columns.Is there any other alternatives other than including those 50 columns in SELECT clause?
Thanks in advance.

Thanks Mike.

Bust is it possible to include columns in SELECT clause which are not in GROUP BY clause?

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 4

No, the only columns you can include in the SELECT clause of a GROUP BY are aggregates, or those used in the GROUP BY. The reason for this is that if you have more than one value, which will you use? Here is an example:

SELECT 'ConstantValue' AS Constant, a, b, MAX(c), SUM(d)

GROUP BY a, b

ORDER BY a, b

What's the problem with including 50 column names in your GROUP BY, if that's the result you're after?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

It looks as if you don’t understand what group by clause does. Can you explain what are trying to do? There is a good chance that you can do it without using group by clause.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 6

Hi Jim and Adi,

Thanks as i have understood what you want to say.