Invalid Column name while running a query

Question

I am new to SQL and I don't know what's wrong in this query,

SELECT 
    wo.WORKORDERID "Request ID", (wo.CREATEDTIME) "Created on", 
    aau.FIRST_NAME "Requester", aac.EMAILID 'From', 
    [To] = STUFF((SELECT ', ' + Recipient_email 
                  FROM workorder_recipients wor2 
                  WHERE wor2.Workorderid = wor.Workorderid and wor2.To_cc_bcc='To' 
                  FOR XML PATH('')), 1, 2, ''), 
    [CC] = STUFF((SELECT ', ' + Recipient_email 
                  FROM workorder_recipients wor2
                  WHERE wor2.Workorderid = wor.Workorderid and wor2.To_cc_bcc='CC' 
                  FOR XML PATH('')), 1, 2, ''),
    cd.CATEGORYNAME "Category" 
FROM 
    workorder_recipients wor
LEFT JOIN 
    workorder wo ON wor.workorderid = wo.workorderid 
LEFT JOIN 
    ModeDefinition mdd ON wo.MODEID = mdd.MODEID 
LEFT JOIN 
    SDUser sdu ON wo.REQUESTERID = sdu.USERID 
LEFT JOIN 
    AaaUser aau ON sdu.USERID = aau.USER_ID 
LEFT JOIN 
    SDUser crd ON wo.CREATEDBYID = crd.USERID 
LEFT JOIN 
    AaaUser cri ON crd.USERID = cri.USER_ID 
LEFT JOIN 
    AaaUserContactInfo aauc ON aau.USER_ID = aauc.USER_ID 
LEFT JOIN 
    AaaContactInfo aac ON aauc.CONTACTINFO_ID = aac.CONTACTINFO_ID 
LEFT JOIN 
    WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID 
LEFT JOIN 
    CategoryDefinition cd ON wos.CATEGORYID = cd.CATEGORYID 
WHERE 
    mdd.MODENAME = 'E-Mail'
    AND cd.CATEGORYNAME in ('Agent Operational Technology (EMEA/UK/IE)','Client Technology')
    AND wo.IS_CATALOG_TEMPLATE='0'
    AND wo.CREATEDTIME >= 1416783600000 
    AND wo.CREATEDTIME <= 1417388399000
    AND wo.ISPARENT='1' 
GROUP BY 
    wo.workorderid 

But I keep getting this error:

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

Thanks, Atul

Answer 1

Imagine the following simple table (T) where ID is the primary key:

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |
2   |    A    |    Y     |

Then you write the following query

SELECT  ID, Column1, Column2
FROM    T
GROUP BY Column1;

This breaks the SQL Standard, and if it were to run without errors (which it would in MySQL), the result:

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |

Is no more or less correct than

ID  | Column1 | Column2  |  
----|---------+----------|
2   |    A    |    Y     |

So what you are saying is give me one row for each distinct value of Column1, which both results sets satisfy, so how do you know which one you will get? Well you don't.

For simplicity sake (and the way it is implemented in SQL Server) we state the rule that if an column is not contained in an aggregate function, it must be in the GROUP BY clause for it to appear in the select list. This is not strictly true, the SQL-Standard does allow columns in the select list not contained in the GROUP BY or an aggregate function, however these columns must be functionally dependent on a column in the GROUP BY. From the SQL-2003-Standard (5WD-02-Foundation-2003-09 - page 346) - http://www.wiscorp.com/sql_2003_standard.zip

15) If T is a grouped table, then let G be the set of grouping columns of T. In each contained in , each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a whose aggregation query is QS.

For example, ID in the sample table is the PRIMARY KEY, so we know it is unique in the table, so the following query conforms to the SQL standard and would run in MySQL and fail in many DBMS currently (At the time of writing Postgresql is the closest DBMS I know of to correctly implementing the standard - Example here):

SELECT  ID, Column1, Column2
FROM    T
GROUP BY ID;

Since ID is unique for each row, there can only be one value of Column1 for each ID, one value of Column2 there is no ambiguity about what to return for each row. As far as I know, Postgresql is the only DBMS that has gone anyway to implementing this.

In order for your query to work you would need to add some columns to the GROUP BY:

GROUP BY wo.workorderid, wo.CREATEDTIME, aau.FIRST_NAME, aac.EMAILID, cd.CATEGORYNAME

However, I think you can remove the issue of duplicates by removing workorder_recipients from your FROM, you don't appear to use this anywhere. Removing this reference should remove the need for GROUP BY

SELECT 
    [Request ID] = wo.WORKORDERID, 
    [Created on] = wo.CREATEDTIME, 
    [Requester] = aau.FIRST_NAME, 
    [From] = aac.EMAILID, 
    [To] = STUFF((SELECT ', ' + Recipient_email 
                  FROM workorder_recipients wor2 
                  WHERE wor2.Workorderid = wo.Workorderid 
                  AND wor2.To_cc_bcc='To' 
                  FOR XML PATH('')), 1, 2, ''), 
    [CC] = STUFF((SELECT ', ' + Recipient_email 
                  FROM  workorder_recipients wor2
                  WHERE wor2.Workorderid = wo.Workorderid 
                  AND   wor2.To_cc_bcc='CC' 
                  FOR XML PATH('')), 1, 2, ''),
    [Category] = cd.CATEGORYNAME 
FROM workorder wo
    LEFT JOIN ModeDefinition AS mdd 
        ON wo.MODEID = mdd.MODEID 
    LEFT JOIN SDUser AS sdu 
        ON wo.REQUESTERID = sdu.USERID 
    LEFT JOIN AaaUser AS aau 
        ON sdu.USERID = aau.USER_ID 
    LEFT JOIN SDUser AS crd 
        ON wo.CREATEDBYID = crd.USERID 
    LEFT JOIN AaaUser AS cri 
        ON crd.USERID = cri.USER_ID 
    LEFT JOIN AaaUserContactInfo AS aauc 
        ON aau.USER_ID = aauc.USER_ID 
    LEFT JOIN AaaContactInfo AS aac 
        ON aauc.CONTACTINFO_ID = aac.CONTACTINFO_ID 
    LEFT JOIN WorkOrderStates AS wos 
        ON wo.WORKORDERID = wos.WORKORDERID 
    LEFT JOIN CategoryDefinition AS cd 
        ON wos.CATEGORYID = cd.CATEGORYID 
WHERE 
    mdd.MODENAME = 'E-Mail'
    AND cd.CATEGORYNAME in ('Agent Operational Technology (EMEA/UK/IE)','Client Technology')
    AND wo.IS_CATALOG_TEMPLATE='0'
    AND wo.CREATEDTIME >= 1416783600000 
    AND wo.CREATEDTIME <= 1417388399000
    AND wo.ISPARENT='1';

Answer 2

when you use GROUP BY in a query, you need to include every field in the group by which is in the select, except ones where you're aggregating - such as a SUM a MIN or a MAX (Amongst others).

So, to contrive an example, this would be invalid:

SELECT FirstName, LastName, SUM(Score)
FROM HighScores
GROUP BY FirstName

You would also need to include LastName in the GROUP BY to get the sum of a person's scores