Shortening GROUP BY Clause

Question

Is it possible to shorten a group by clause so that you do not have to repeat the fields mentioned in the select clause ad nauseam? For example:

SELECT
   field1,
   field2,
   field3,
   field4
FROM table
GROUP BY
   field1,
   field2,
   field3,
   field4

 to:

SELECT
 field1,
 field2,
 field3,
 field4
FROM table
GROUP BY
 SELECT.*

...or something to this effect. I'm writing a query that will be utilizing the sp_executesql() stored procedure and I'm running out of space available in my variable. Thank you much.

Answer 1

are you looking for SELECT DISTINCT or SELECT DISTINCTROW?

Answer 2

SELECT
   field1,
   field2,
   field3,
   field4
FROM table
GROUP BY 1,2,3,4

Where the numbers represent the colum position in select part.

Answer 3

They are 2 different concepts:

• SELECT = output
• GROUP BY = what to aggregate over

Now, each SELECT entry must be aggregated or in the GROUP BY. Often, the GROUP BY also has more columns that the SELECT.

However, I suspect that you are aggregating too much/too late. You can use derived tables or CTE to do things "earlier".

SELECT 
   T1.field1, T1.field2, foo.field3, foo.field4, foo.AggField5
FROM
   table T1
   JOIN
   (SELECT
        T2.keycol, T2.field3, T2.field4, SUM(T2.field5) AS AggField5
   FROM 
        table T2
   GROUP BY 
        T2.keycolT2.field3, T2.field4
   ) foo ON T1.Keycol = foo.keycol

In your example though , use DISTINCT without GROUP BY...

Answer 4

There is a significant difference between DISTINCT and GROUP BY. In simple queries they might be the same, although of course, GROUP BY allows aggregates.

But as well as this, DISTINCT will work out every field before doing the DISTINCT, even if they are deterministic uses of the same field (eg, StartDate and DATEADD(day,1,StartDate) - check the Execution Plan, you'll see that the DATEADD is done BEFORE the DISTINCT. So GROUP BY is almost always better.

But... it's worth bearing in mind that as soon as GROUP BY includes something which has a Unique Index on it (such as the Primary Key), any other items from that table are ignored. After all, you can't get any more unique by including other fields in the GROUP BY clause.

So why not take an approach of:

GROUP BY t.id, u.id
/* All these other fields are ignored but here because they're 
   in the HAVING/SELECT/ORDER BY clauses
*/
, t.TransactionValue, u.Username, ....

I get that you're still having to type it all out, and it would be nice to have GROUP BY t.*, but if you want, you could let Microsoft know by voting at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124654