How can I group this ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I group this ?

Author

Topic

steve4134
Starting Member

24 Posts

Posted - 2011-02-08 : 11:08:08

SELECT (CASE WHEN source = 'Lifeguard' THEN 1 WHEN source = 'Call' THEN 0 END) AS Lifeguard,
(CASE WHEN source = 'Lifeguard' THEN 0 WHEN source = 'Call' THEN 1 END) AS CRM, DATEADD(d, 0, DATEDIFF(d, 0, creation_date))
AS [Creation Date], contact_phone_id AS [Contact Phone Id]
FROM cstd_contact_phone_sms
WHERE (opt_in = N'y')

ORDER BY [Creation Date]

=====================================================================

Literally all I want to do is GROUP BY the contact_phone_id. This is the error I get when I do GROUP BY contact_phone_id

======================================================================
Msg 8120, Level 16, State 1, Line 1
Column 'cstd_contact_phone_sms.source' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'cstd_contact_phone_sms.source' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

--------------------------------------------------------------------
Any help is appreciated

Steve

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-08 : 11:18:01

GROUP BY
CASE WHEN source = 'Lifeguard' THEN 1 WHEN source = 'Call' THEN 0 END
,CASE WHEN source = 'Lifeguard' THEN 0 WHEN source = 'Call' THEN 1 END
, DATEADD(d, 0, DATEDIFF(d, 0, creation_date))
, contact_phone_id

or, you can put a max() around each of the case statements and then group by contact_phone_id

Jim

Everyday I learn something that somebody else already knew

steve4134
Starting Member

24 Posts

Posted - 2011-02-08 : 11:32:03

Jim

Can you show me how to do the max() please . I would appreciate it greatly.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-08 : 11:42:19

SELECT MAX(CASE WHEN source = 'Lifeguard' THEN 1 WHEN source = 'Call' THEN 0 END) AS Lifeguard,
MAX(CASE WHEN source = 'Lifeguard' THEN 0 WHEN source = 'Call' THEN 1 END) AS CRM, MAX(DATEADD(d, 0, DATEDIFF(d, 0, creation_date)) )

Jim

Everyday I learn something that somebody else already knew

steve4134
Starting Member

24 Posts

Posted - 2011-02-08 : 11:57:38

SELECT MAX((CASE WHEN source = 'Lifeguard' THEN 1 WHEN source = 'Call' THEN 0 END)) AS Lifeguard,MAX(
(CASE WHEN source = 'Lifeguard' THEN 0 WHEN source = 'Call' THEN 1 END)) AS CRM, MAX(DATEADD(d, 0, DATEDIFF(d, 0, creation_date)))
AS [Creation Date], contact_phone_id AS [Contact Phone Id]
FROM cstd_contact_phone_sms
WHERE (opt_in = N'y')
GROUP BY contact_phone_id
ORDER BY [Creation Date]

steve4134
Starting Member

24 Posts

Posted - 2011-02-08 : 11:58:00

This is what I got. Thanks again Jim

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources