I'm getting 'Column name is invalid in the select list' error when i execute the query

Question

This is my query,

SELECT TOP (3) 
    a.INCIDENT_ID, a.REFERENCE, SUM(b.COST) AS TOTAL_COST
FROM          
    FDDC_T_D_INCIDENT AS a 
INNER JOIN
    FDDC_T_D_INCIDENT_COST AS b ON a.INCIDENT_ID = b.FINCIDENT_ID
WHERE        
    (a.CREATED_DATE >= @date1) AND (a.CREATED_DATE <= @date2)
GROUP BY 
    a.INCIDENT_ID
ORDER BY 
    TOTAL_COST DESC

I'm getting an error

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

Can someone please help me with this issue?

Thanks in advance!

The general group by rule is: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function". So either list that column in the GROUP BY clause, or use it as argument to a set function! — jarlh, Apr 10 '15 at 12:25
quite clear what is wrong, just add `a.Reference` to the group by.. — Ric, Apr 10 '15 at 12:25
All you really needed to do was read the error message--it tells you what to do. — Russ, Apr 10 '15 at 12:26
@Madushi, please clarify you desired results. If you have multiple rows for the same `INCIDENT_ID` but different `REFERENCE` values, what `REFERENCE` value should be returned in the result since the `GROUP BY` is on `INCIDENT_ID` alone. — Dan Guzman, Apr 10 '15 at 12:42
how many reference do you have per `INCIDENT_ID` ? and if you have more than one, which do you want to select? — A ツ, Apr 10 '15 at 12:54
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:36
Possible duplicate of [GROUP BY / aggregate function confusion in SQL](https://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) — andrews, Nov 27 '17 at 16:16

PKirby · Answer 1 · 2015-04-10T12:43:29.730

0

Try this :

SELECT        TOP (3) a.INCIDENT_ID, a.REFERENCE, SUM(b.COST) AS TOTAL_COST
FROM          FDDC_T_D_INCIDENT AS a INNER JOIN
              FDDC_T_D_INCIDENT_COST AS b ON a.INCIDENT_ID =b.FINCIDENT_ID
WHERE        (a.CREATED_DATE >= @date1) AND (a.CREATED_DATE <= @date2)
GROUP BY a.INCIDENT_ID, a.REFERENCE
ORDER BY TOTAL_COST DESC

You need to add 'a.REFERENCE' to the GROUP BY clause.

EDIT :

Please try this if you only want to GROUP BY 'a.INCIDENT_ID' :

SELECT INCIDENT_ID, REFERENCE, TOTAL_COST
FROM(
        SELECT        TOP (3) a.INCIDENT_ID, a.REFERENCE, SUM(b.COST) AS TOTAL_COST
        FROM          FDDC_T_D_INCIDENT AS a INNER JOIN
                      FDDC_T_D_INCIDENT_COST AS b ON a.INCIDENT_ID =b.FINCIDENT_ID
        WHERE        (a.CREATED_DATE >= @date1) AND (a.CREATED_DATE <= @date2)
        GROUP BY a.INCIDENT_ID, a.REFERENCE
        ORDER BY TOTAL_COST DESC
    ) AS T
GROUP BY INCIDENT_ID

Hope this helps.

edited Apr 10 '15 at 12:43

answered Apr 10 '15 at 12:25

PKirby

859
3
16
36

yah it works. But i want to group only using a.INCIDENT_ID. So can you please tell me, how to change this query to group only using a.INCIDENT_ID. – Madushi Apr 10 '15 at 12:30
When I execute your query, it is giving an error saying 'The multi-part identifier ' "a.INCIDENT_ID" could not be found' and 'The multi-part identifier ' "a.REFERENCE" could not be found' - – Madushi Apr 10 '15 at 12:42
I'm sorry to say, but now it is giving an error 'Column "T.REFERENCE" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause' – Madushi Apr 10 '15 at 12:49

score 0 · Answer 2 · answered Apr 10 '15 at 19:08

Try this, to group by Incident_ID and return reference as well (will return multiple rows with the same total cost if the incident has multiple references).

SELECT TOP (3) 
    a.INCIDENT_ID, a.REFERENCE, SUM(b.COST) over (partition by a.incident_ID) AS TOTAL_COST
FROM          
    FDDC_T_D_INCIDENT AS a 
INNER JOIN
    FDDC_T_D_INCIDENT_COST AS b ON a.INCIDENT_ID = b.FINCIDENT_ID
WHERE        
    (a.CREATED_DATE >= @date1) AND (a.CREATED_DATE <= @date2)
ORDER BY 
    TOTAL_COST DESC

I'm getting 'Column name is invalid in the select list' error when i execute the query

2 Answers2