CODEWITHSUNDEEP
sql-server
Paolo Broccardo
Paolo Broccardo

Reputation: 1744

Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

My query below runs fine without the MAX(colName) lines. The original query selects about 100 columns, but now the MAX(colName) columns need to be added. Obviously when I add them, MS SQL complains with the error:

"Column 'applicationId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Is there any way to add these computed value columns without having to change the other 100 columns in the select?? The example below is simplified but the original query is a lot bigger and more complex. 

                SELECT 
                     g.applicationId,
                     -- (another 100 or so columns just like above)
                    -- max(g.AScore) as AScore,
                    -- max(g.APercentile) as APercentile
                FROM application a
                LEFT JOIN GREScores g ON a.applicationId = g.applicationId
                WHERE g.applicationID = 1

Thanks

UPDATE

Looks like the subquery approach mentioned by @OVais did the trick. If you believe this is not a good approach, please tell me why: 

                SELECT 
                     g.applicationId,
                     -- (another 100 or so columns just like above)
                    (SELECT MAX(AScore) FROM GREScores WHERE GREScores.applicationId = a.applicationId) AS tAScore
                    -- max(g.APercentile) as APercentile
                FROM application a
                LEFT JOIN GREScores g ON a.applicationId = g.applicationId
                WHERE g.applicationID = 1

Upvotes: 3

Views: 27745

Answers (5)

gbn
gbn

Reputation: 432712

The GROUP BY is there to ensure the query is semantically correct. What if you have 14 different values for column56: what should SQL Server guess that you want 14 rows in the output or collapse to MAX?

The SQL standard requires the GROUP BY to be populated (of mainstream RDBMS, only MySQL doesn't and makes a guess to resolved the ambiguity).

Now, there are ways around this:

  • copy/paste from SELECT list to GROUP BY
  • drag from the table column node which generates a CSV list in the query editor

Edit:

My answer above is for "MAX per grouping of the 100 columns".

If you want "All rows, with a single MAX for all rows" then you can use windowing

        SELECT 
             g.applicationId,
             -- (another 100 or so columns just like above)
            max(g.AScore) OVER () as AScore,
            max(g.APercentile) OVER () as APercentile
        FROM GREScores g
        WHERE g.applicationID = 1

Upvotes: 8

Madhivanan
Madhivanan

Reputation: 13700

Corelated subquery is one of methods to do this

Upvotes: 0

EricZ
EricZ

Reputation: 6205

;WITH cte AS
(
 SELECT *
 FROM GREScores g
 WHERE g.applicationID = 1
)
SELECT 
     g.applicationId,
     -- (another 100 or so columns just like above)
    AScore =(select  max(g2.AScore) FROM cte g2) ,
    APercentile =(select  max(g2.APercentile) FROM cte g2)
FROM cte g

Upvotes: 1

Ovais Khatri
Ovais Khatri

Reputation: 3211

You can use subquery instead of using aggregate function.. select col1,col2,(select Sum(col3) from table_name) as Sum from table_name

Upvotes: 3

Randy
Randy

Reputation: 16673

Max is an aggregate function

you need to add a GROUP BY for all the other columns at the end...

Upvotes: 1

Related Questions