SQL Server GROUP BY troubles!
- by Lucas311
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
