1

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?
Community
  • 1
  • 1
p0tato
  • 71
  • 2
  • 10
  • I see no problem with that. Unless the data type is an int an you comparing a string to an int – mvisser Jun 17 '16 at 15:58
  • The problem is you can't use two data types in SQL case statements. It can only return one data type and there's a preference order in how it picks that. Use Gordon Linoffs answer. https://msdn.microsoft.com/en-us/library/ms181765.aspx Search for data types in that post if you want more info. – Sam Jun 17 '16 at 16:08
  • @Sam Gordon's answer didn't work, please see my comment to it – p0tato Jun 27 '16 at 20:10
  • Do you have to remove all count statements for this query to work or did you try removing each count statement to see which causes the problem? – Sam Jun 27 '16 at 23:33
  • Also check the fields logtye and category in sm_aggregate_log table to see which contains 'dynamic-preview-7179' – Sam Jun 27 '16 at 23:39
  • @Sam each one causes the error message. Neither the category or logtype field contain 'dynamic-preview-7179', I even used a 'like' query. I feel like i've tried everything! D: – p0tato Jun 28 '16 at 17:14
  • If logtype and category are supposed to have only integers can you add the below also in the where condition and see how it goes. ISNUMERIC(ag.logtype) = 1 AND ISNUMERIC(ag.category) = 1 – Sam Jun 28 '16 at 17:22
  • @Sam that did't work :< I appreciate your dedication – p0tato Jun 29 '16 at 20:14

2 Answers2

1

According to your description of the data types, this should work:

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

Numbers should be compared to numeric constants; strings to string constants.

Although equivalent, I would write the logic as:

sum(case when ag.logtype = 7 then 1 else 0 end) as Unsubs,

Why? Two reasons that are merely preferences:

  • I prefer the more general case statement because I find that when modifying code, I often need to add in new conditions. I prefer IN to using multiple WHENs.
  • I prefer sum() over count() because count(2) = count(1).
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I did try that, however I just copied and pasted your code to double check. I still received the same error: "Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'dynamic-preview-7179' to data type int." – p0tato Jun 17 '16 at 17:51
  • @z0nia . . . In that case, the problem would not be the aggregations. I think the obvious conversion possibility is the `join` condition: ` ms.mailing_id = ag.mailingid`. – Gordon Linoff Jun 19 '16 at 16:43
  • I should have added that when I remove the 6 count statements, the query works! (That being said, ms.mailing_id is int and ag.mailingid is varchar) – p0tato Jun 20 '16 at 13:35
  • any other suggestions? – p0tato Jun 27 '16 at 16:17
  • @z0nia . . . Nothing comes to mind. If those types are as you say, I don't see how the `COUNT()` would generate an error. – Gordon Linoff Jun 28 '16 at 03:02
0

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

p0tato
  • 71
  • 2
  • 10