z0nia z0nia - 5 months ago 35
SQL Question

SQL Case and Cast in Count function

I am trying to count records from two fields only if they meet a specific criteria.


This [Is it possible to specify condition in Count()? ] post was helpful, but it doesn't account for casting varchar to int.


Here is my code:


SELECT Mailing_Id ,Mailing_Nm,Subject_Line,Campaign_Nm,Start_Ts,End_Ts, Mailed_Cnt, Invalid_Cnt ,Actual_Sent_Cnt ,Bounce_Cnt ,Open_Cnt ,Click_Cnt
,count(case ag.logtype when '7' then 1 end) as Unsubs
,count(case ag.category when '1' then 1 end) as Block
,count(case ag.category when '2' then 1 end) as Hard
,count(case ag.category when '3' then 1 end) as Soft
,count(case ag.category when '4' then 1 end) as Tech
,count(case ag.category when '9' then 1 end) as Unknown
FROM [StrongMailTracking].[dbo].[SM_MAILING_SUMMARY] ms left join sm_aggregate_log ag on ms.mailing_id = ag.mailingid
WHERE datepart(year,start_ts) = 2015 and (mailing_nm not like '%delivery report%' and mailing_nm not like '%daily helpdesk%' and mailing_nm not like '%test%')
GROUP BY Mailing_Id ,Mailing_Nm ,Subject_Line ,Campaign_Nm ,Start_Ts ,End_Ts ,Mailed_Cnt ,Invalid_Cnt ,Actual_Sent_Cnt ,Bounce_Cnt ,Open_Cnt ,Click_Cnt
ORDER BY mailing_id asc



Please draw your attention to the 6 case statements. Logtype is int, Category is varchar.

I've tried:



  • removing the single quotes

  • adding ... case cast( - as int) when ...

  • removing single quotes while casting

  • casting as numeric first then int

    But I keep getting this error: "Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'dynamic-preview-7179' to data type int."



    Does anyone have ideas on what to do?


Answer

as I commented before, ms.Mailing_Id is an int and ag.mailingid is a varchar. a colleague helped me out with this:

FROM [StrongMailTracking].[dbo].[SM_MAILING_SUMMARY] ms left join sm_aggregate_log ag on CAST(ms.mailing_id As varchar(255)) = ag.mailingid