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

Question

I have two database tables, project and story. The one project having different types of story. I wanted to display all project with each type story count.

This is my SQL:

SELECT
     p.id,
     p.proj_id,
     p.proj_title,
     p.proj_status,
     COUNT(CASE WHEN s.story_type = 'TY_FEATURES' THEN 1 END) AS FEATURES,
     COUNT(CASE WHEN s.story_type = 'TY_ENHANCEMENTS' THEN 1 END) AS ENHANCEMENTS,
     COUNT(CASE WHEN s.story_type = 'TY_DEPENDENCIES' THEN 1 END) AS DEPENDENCIES,
     COUNT(CASE WHEN s.story_type = 'TY_ADHOC' THEN 1 END) AS ADHOC,
     COUNT(CASE WHEN s.story_type = 'TY_DEMO' THEN 1 END) AS DEMO,
     COUNT(CASE WHEN s.story_type = 'TY_IMPROVEMENTS' THEN 1 END) AS IMPROVEMENTS
FROM
     project p LEFT JOIN story s
ON
     p.proj_id = s.proj_id
GROUP BY
     p.id,
     p.proj_id,
     p.proj_status,
     p.proj_title

The SQL Query working fine, but I wanted to display all column from project table. If the column added in SELECT like p.newExistingInTable. I need to add in GROUP BY too, how can I make flexible? or any alternative optimized SQL query?

Error showing if new column added only in SELECT:

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

I can't see `newExistingInTable` in your query - can you edit to the form that produces the error you specify? — halfer, Jul 24 '15 at 09:17
try [searching the site for that error](http://stackoverflow.com/search?q=%5Bsql-server%5D+is+invalid+in+the+select+list+because+it+is+not+contained+in+either+an+aggregate+function+or+the+GROUP+BY+clause) and you'll see dozons of answers — Tanner, Jul 24 '15 at 09:23
possible duplicate of [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](http://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — Tanner, Jul 24 '15 at 09:24

score 2 · Accepted Answer · answered Jul 24 '15 at 09:24

You can generate the story summaries inside a subquery:

SELECT
    p.id,
    p.proj_id,
    p.proj_title,
    p.proj_status,
    FEATURES,
    ENHANCEMENTS,
    DEPENDENCIES,
    ADHOC,
    DEMO,
    IMPROVEMENTS
FROM
    project p LEFT JOIN
    (SELECT proj_id,
       COUNT(CASE WHEN story_type = 'TY_FEATURES' THEN 1 END) AS FEATURES,
       COUNT(CASE WHEN story_type = 'TY_ENHANCEMENTS' THEN 1 END) AS ENHANCEMENTS,
       COUNT(CASE WHEN story_type = 'TY_DEPENDENCIES' THEN 1 END) AS DEPENDENCIES,
       COUNT(CASE WHEN story_type = 'TY_ADHOC' THEN 1 END) AS ADHOC,
       COUNT(CASE WHEN story_type = 'TY_DEMO' THEN 1 END) AS DEMO,
       COUNT(CASE WHEN story_type = 'TY_IMPROVEMENTS' THEN 1 END) AS IMPROVEMENTS
   FROM story
   GROUP BY proj_id) s
        ON
             p.proj_id = s.proj_id

Carsten Massmann · Answer 2 · 2015-07-24T09:34:19.470

-1

You could use individual subqueries for each count:

SELECT id,proj_id, proj_title,proj_status,
   -- any other columns from project ...
   (SELECT COUNT(*) FROM story s 
    WHERE s.story_type='TY_FEATURES'  
    AND s.proj_id=p.proj_id) FEATURES,
   (SELECT ... other story counts ) colname
 FROM project p

That way no JOIN to story is necessary and you don't need group by either. But of course you need to link each subquery properly to the main record by two where conditions.

edited Jul 24 '15 at 09:34

answered Jul 24 '15 at 09:24

Carsten Massmann

26,510
2
22
43

It's working, but writing subqueries for each count, it's not looking good for performance perspective. – Sachink Jul 24 '15 at 11:09
Fair enough. The performance depends of course on the number of 'count' columns you will need. On MS-SQL server I have similar queries in production and the performance compares well to the `CASE/GROUP BY` solution (I tested them both there). – Carsten Massmann Jul 24 '15 at 11:14
Thanks, I will consider your suggestions and experience in my work. thank you so much. – Sachink Jul 24 '15 at 11:54
I would be interested in the reasons for the downvotes. Subqueries always come at a cost. Whether the performance is going to be worse depends on the number of these subqueries compared to the total number of records the `case/inner join/group by` would have to plough through. – Carsten Massmann Jul 24 '15 at 13:22

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

2 Answers2