SQL Server GROUP BY troubles!

I'm getting a frustrating error in one of my SQL Server 2008 queries. It parses fine, but crashes when I try to execute. The error I get is the following:

Msg 8120, Level 16, State 1, Line 4

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

SELECT *
FROM   (SELECT ctr.sp_id                     AS spid,
           Substring(ctr.company, 1, 20) AS company,
           cci.email_address             AS tech_email,
           CASE
             WHEN rating IS NULL THEN 'unknown'
             ELSE rating
        开发者_开发技巧   END                           AS rating
    FROM   customer_contactinfo cci
           INNER JOIN customertraffic_return ctr
             ON ctr.sp_id = cci.sp_id
    WHERE  cci.email_address <> ''
           AND cci.email_address NOT LIKE '%hotmail%'
           AND cci.email_address IS NOT NULL
           AND ( region LIKE 'Europe%'
                  OR region LIKE 'Asia%' )
           AND SERVICE IN ( '1', '2' )
           AND ( rating IN ( 'Premiere', 'Standard', 'unknown' )
                  OR rating IS NULL )
           AND msgcount >= 5000
    GROUP  BY ctr.sp_id,
              cci.email_address) AS a
WHERE  spid NOT IN (SELECT spid
                FROM   customer_exclude)
GROUP  BY spid,
      tech_email  

---

Well, the error is pretty clear, no??

You're selecting those columns in your inner SELECT:

• spid
• company
• tech_email
• rating

and your grouping only by two of those (GROUP BY ctr.sp_id, cci.email_address).

Either you need group by all four of them (GROUP BY ctr.sp_id, cci.email_address, company, rating), or you need to apply an aggregate function (SUM, AVG, MIN, MAX) to the other two columns (company and rating).

Or maybe using a GROUP BY here is totally the wrong way to do - what is it you're really trying to do here??

---

The inner query:

    SELECT ctr.sp_id                     AS spid,
           Substring(ctr.company, 1, 20) AS company,
           cci.email_address             AS tech_email,
           CASE
             WHEN rating IS NULL THEN 'unknown'
             ELSE rating
           END                           AS rating
    FROM   customer_contactinfo cci
           INNER JOIN customertraffic_return ctr
             ON ctr.sp_id = cci.sp_id
    WHERE  cci.email_address <> ''
           AND cci.email_address NOT LIKE '%hotmail%'
           AND cci.email_address IS NOT NULL
           AND ( region LIKE 'Europe%'
                  OR region LIKE 'Asia%' )
           AND SERVICE IN ( '1', '2' )
           AND ( rating IN ( 'Premiere', 'Standard', 'unknown' )
                  OR rating IS NULL )
           AND msgcount >= 5000
    GROUP  BY ctr.sp_id,
              cci.email_address

has 4 non-aggregate things in the select (sp_id, company, email_address, rating) and you only group on two of them, so it is throwing an error on the first one it sees

So you either need to not group by any of them or group by all of them

---

i suggest replacing the * with a fully specified column list.

---

you can either group by all selected columns or use the other columns (not in group by clause) in a aggregate function (like sum)

you cannot: select a,b,c from bla group by a,b but you can: select a,b,sum(c) from bla groupy by a,b