Efficient way to run count

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  December 7, 2010 at 9:41 am

  #232160

  Hi,

  I am trying to get a set of count results from about 80 bit fields in 1 table of about 250k rows.

  I am using the following e.g.

  select

  ORDEREDCount = (select count(ORDERED) from abstract where ORDERED=1),

  CATALOGUEDCount = (select count(CATALOGUED) from abstract where CATALOGUED=1),

  PRINTEDCount = (select count(PRINTED) from abstract where PRINTED=1),

  PHOTOCOPIEDCount = (select count(PHOTOCOPIED) from abstract where PHOTOCOPIED=1),

  PDFCount = (select count(PDF) from abstract where PDF=1)

  etc etc

  Is this the most efficient way to run it. Or should I go with the union all route. I have tried to use the database engine tuning advisor but it likes to crash when I ask for its help on this one.

  Many Thanks for your help/comments in advance.

  Thanks,

  Oliver

• sturner

  SSC-Insane

  Points: 22411

  More actions

  December 7, 2010 at 1:20 pm

  #1259765

  This would be the fastest:

  select

  ORDEREDCount = sum(case when ORDERED=1 then 1 else 0 end),

  CATALOGUEDCount = sum(case when CATALOGUED=1 then 1 else 0 end),

  PRINTEDCount = sum(case when PRINTED=1 then 1 else 0 end),

  etc. etc.

  The probability of survival is inversely proportional to the angle of arrival.

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  December 8, 2010 at 2:31 am

  #1259951

  Thank you very much, the query now takes less than a second instead of 5-6 seconds.

  Kind Regards,

  Oliver