Null value is eliminated by an aggregate or other

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

Null value is eliminated by an aggregate or other

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-05-05 : 16:02:55

I have the following but get this error in the results:


Select a.CPN, b.keys, 


                COUNT (CASE WHEN a. Codes LIKE '%745%'
                                    OR      a. Codes LIKE '%737%'
                                    OR      a. Codes LIKE '%469%'
                                    THEN a. Codes 
                    END) AS 'Moto', 


FROM   history a, mail.DBO.records b
WHERE                 a.leadrecordid = b.leadrecordid
and a.date >= '20100401'
and a.date <= '20100430' 
and a.Codes <>'' 
and a.orders IN ('Good','Great')
group by a.CPN, b.keys

what does it mean?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-05 : 16:12:03

I think I know what you're trying to achieve. Try this:

Select a.CPN, b.keys, 


                SUM (CASE WHEN a. Codes LIKE '%745%'
                                    OR      a. Codes LIKE '%737%'
                                    OR      a. Codes LIKE '%469%'
                                    THEN 1 ELSE 0 
                    END) AS 'Moto', 


FROM   history a, mail.DBO.records b
WHERE                 a.leadrecordid = b.leadrecordid
and a.date >= '20100401'
and a.date <= '20100430' 
and a.Codes <>'' 
and a.orders IN ('Good','Great')
group by a.CPN, b.keys

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-05-05 : 16:17:24

What does that do?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-05 : 16:25:04

It returns a count of the records where Codes LIKE '%745%' OR a. Codes LIKE '%737%' OR a. Codes LIKE '%469%'.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources