Count expression help

• andy.gear

  SSC-Addicted

  Points: 493

  More actions

  January 26, 2009 at 7:56 am

  #128177

  I'm probably making this harder than it needs to be. I have a data set that looks like:

  ItemNo, Date1, Date2

  I want to group them by ItemNo and get a count by ItemNo where Date1>Date2 as well as a count of how many rows have that item number (so I can get a percentage in a later report of how many times Date1 is greater than Date2). Here is what want, but count doesn't want to work this way:

  select ItemNo, count(Date1 > Date2), count(1) from Table group by ItemNo

  How should I do this?

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 26, 2009 at 8:15 am

  #932611

  select ItemNo,

  sum(case when Date1 > Date2 then 1 else 0 end),

  count(*)

  from Table

  group by ItemNo

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537