Invalid column name during query execution

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 violates the SQL standard, and if it should run without error (which it would in MySQL), the result is:

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

      

        
        
        
      

    

No more or less correct than

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

      

        
        
        
      

    

So what are you saying, give me one row for each distinct value Column1

that satisfies both sets of results, so how do you know which one you will get? Oh no.

For simplicity (and how this is implemented in SQL Server), we specify a rule that if a column is not contained in an aggregated function, it must be in the GROUP BY clause for it to appear in the select list. This is not entirely true, the SQL standard allows columns in the select list that are not contained in the GROUP BY or aggregate function, however, these columns must be functionally dependent on the column in the GROUP BY. From SQL-2003-Standard (5WD-02-Foundation-2003-09 - p. 346) - http://www.wiscorp.com/sql_2003_standard.zip

15) If T is a grouped table, then let G be the set of columns of the grouping T. Each of them contains in, each column reference that refers to a column T must refer to some column C that functionally depends on G or is contained in an aggregated argument whose aggregation request is QS.

For example, the id in the sample table is PRIMARY KEY, so we know that it is unique in the table, so the following query conforms to the SQL standard and will work in MySQL and will not work in many RDBMSs currently (at the time of writing Postgresql is the closest DBMS that I know to implement the standard correctly - Example here ):

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

      

        
        
        
      

    

Since the identifier is unique for each row, there can be only one value for each identifier Column1

, one value 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 for it anyway.

For your query to work, you need to add multiple columns to GROUP BY

:

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

      

        
        
        
      

    

However, I think you can remove the duplicates problem by removing workorder_recipients

from yours FROM

, you don't seem to be using it anywhere. Removing this link should eliminate the need forGROUP 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';