How to count my checkboxes[Answered] RSS

• • Reply
  Dan5

  Member

  64 Points

  262 Posts

  How to count my checkboxes

  May 19, 2020 02:18 PM|Dan5|LINK

  Hello,

  I have one table, that captures the location  and month as well as several error type represented as checkboxes. I would like to count the number of errors each month based on location, but I am not sure on how to write the SQL 

  Result should look like this:

  Month  Location ErrorType1 ErrorTpye2 ErrorType3
  May      LOC1           4                  7                20
  May     LOC2            5                12                  3
  June     LOC1           23                3                   55

  I tried something list this,but its not working

  SELECT Location,datename(month,Timestamp1)as Month,COUNT(error1) AS ErrorType1 ,COUNT(Error2) as ErrorType2,COUNT(Error3) as ErrorType3
  FROM dbo.Errors
  WHERE Error1='True' or Error2='true' or Error3='true'
  GROUP BY datename(month,timestamp1),Location

  Any idea how to do this?..thanks

• • Reply
  imapsp

  Participant

  1120 Points

  296 Posts

  Re: How to count my checkboxes

  May 19, 2020 02:39 PM|imapsp|LINK

  Hi,

  Try:

  COUNT(case when Error1 = 'True' then 1 end) AS ErrorType1,
  COUNT(case when Error2 = 'True' then 1 end) as ErrorType2,
  COUNT(case when Error3 = 'True' then 1 end) as ErrorType3

  Hope this help

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  limno

  All-Star

  123252 Points

  10024 Posts

  Moderator

  Re: How to count my checkboxes

  May 19, 2020 02:40 PM|limno|LINK

  SELECT Location,datename(month,Timestamp1)as [Month]
  ,SUM(Case when  Error1='True' then 1 else 0 end) AS ErrorType1 
  ,SUM(Case when  Error2='True' then 1 else 0 end) AS ErrorType2 
  ,SUM(Case when  Error3='True' then 1 else 0 end) AS ErrorType3  
  FROM dbo.Errors
  GROUP BY datename(month,timestamp1),Location

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  Dan5

  Member

  64 Points

  262 Posts

  Re: How to count my checkboxes

  May 19, 2020 05:04 PM|Dan5|LINK

  Thanks very much!!

• • Reply
  Dan5

  Member

  64 Points

  262 Posts

  Re: How to count my checkboxes

  May 19, 2020 05:05 PM|Dan5|LINK

  Thanks for your help!