Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to make the following SQL statement work, but I get a syntax error:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*

Here, A is a wide table with 40 columns and I would like to avoid listing each column name in the GROUP BY clause if possible. I have many such tables over which I have to run a similar query, so I will have to write a Stored Procedure. What's the best way to approach this?

I am using MS SQL Server 2008.

share|improve this question

2 Answers 2

up vote 21 down vote accepted

GROUP BY A.* is not allowed in SQL.

You can bypass this by using a subquery where you group by, and then join:

SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
  LEFT JOIN 
      ( SELECT FKey, COUNT(foo) AS cnt
        FROM TABLE2 
        GROUP BY FKey
      ) AS B 
    ON A.PKey = B.FKey ;

There is a feature in SQL-2003 standard to allow in the SELECT list, columns that are not in the GROUP BY list, as long as they are functionally dependent on them. If that feature had been implemented in SQL-Server, your query could have been written as:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.pk                          --- the Primary Key of table A

Unfortunately, this feature has not yet been implemented, not even in SQL-Server 2012 version - and not in any other DBMS as far as I know. Except for MySQL which has it but inadequately (inadequately as: the above query will work but the engine will do no checking for functional dependency and other ill-written queries will show wrong, semi-random results).

As @Mark Byers informed us in a comment, PostgreSQL 9.1 added a new feature designed for this purpose. It is more restrictive than MySQL's implementation.

share|improve this answer
    
Can you mention a couple of RDBMS's that implement that portion of the standard as written? I know, for example, that MySQL will allow you (given the proper settings) to include items not within the GROUP BY clause in the SELECT list, but it leaves it undefined as to which row that value will come from (so if the column or expression isn't functionally dependent upon the grouping expression, then it could come from any row within the group). –  Adam Robinson Jul 20 '12 at 15:08
    
@Adam: No, I know no RDBMS that has it implemented. MySQL has it but inadequately, as your comment says. –  ypercube Jul 20 '12 at 15:10
    
Gotcha. I was actually asking if there were, since I have experience with far fewer RDBMS's than I would imagine most of the people answering questions on this site would have ;) But that was my suspicion. –  Adam Robinson Jul 20 '12 at 15:23
2  
"and not in any other DBMS as far as I know." PostgreSQL 9.1 added a new feature designed for this purpose. It is more restrictive than MySQL's implementation. –  Mark Byers Jul 21 '12 at 7:29
    
@MarkByers: thnx, I didn't know that. –  ypercube Jul 21 '12 at 7:31

In addition to @ypercube's workaround, "typing" is never an excuse for using SELECT *. I've written about this here, and even with the workaround I think your SELECT list should still include the column names - even if there are a massive number like 40.

Long story short, you can avoid typing these big lists by clicking and dragging the Columns node for the object in Object Explorer onto your query window. The screen shot shows a view but the same thing can be done for a table.

enter image description here

But if you want to read about all the reasons why you should subject yourself to this huge level of effort of dragging an item a few inches, please read my post. :-)

share|improve this answer
    
In PostgreSQL (with EMS SQL Manager), I do this defining a view as SELECT * and then copy the field list from the view definition. –  dezso Jul 20 '12 at 13:28
    
I certainly agree that SELECT * should not be used. I'm curious about the GROUP BY case though. @Aaron, are there efficiency issues with having 40 columns in the Group By list? –  ypercube Jul 20 '12 at 13:33
    
@ypercube there can be, sure. Your workaround is much better because it avoids them. I'm curious in what situation there will be a possibility that all 40 columns in a table have the same values on multiple rows. No key? What do these columns mean? What does grouping them mean? Seems very edge case to me. –  Aaron Bertrand Jul 20 '12 at 13:34
1  
@ypercube - As far as I've seen if you group by A.PK, A.some, A.other, A.columns it doesn't bother actually comparing some, other, columns this is just required by the syntax. –  Martin Smith Jul 20 '12 at 14:09
1  
@datagod sorry, no, any gaps could only be explained by the SSMS dev team. :-) –  Aaron Bertrand Jul 20 '12 at 14:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.