vendredi 17 juin 2016

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?

Aucun commentaire:

Enregistrer un commentaire