count(*) giving wrong numbers

• Data_Analyst

  SSC-Addicted

  Points: 438

  More actions

  April 19, 2012 at 8:55 pm

  #259636

  I am trying to show the counts of an activity

  and my query is as following:

  select ID,

  site_id ,

  COUNT(*)OPENCOUNT,

  TO_CHAR(WHEN_OPENED,'MONTH,YYYY')MONTH_DATE

  from OPEN where WHEN_OPENED is not null and TRUNC(O.WHEN_OPENED) between '01-APR-2010'

  and '31-MAR-2012'

  GROUP BY TO_CHAR(WHEN_OPENED,'MONTH,YYYY'),OID_silverpop_open,site_id

  For some reason my counts are coming as 1 for each row , I am lost as how do I count the total activity.

  Columns in table are:

  id,dite_id,when_opened etc but it doesn't have any columns to show the count so I had only one option of doing count(*)

  Any ideas?

  Thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 19, 2012 at 9:03 pm

  #1476389

  Not really, the code you posted is Oracle.

  Plus, not enough information to give you an answer.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 20, 2012 at 3:49 am

  #1476491

  Taking a guess since I don't know Oracle code (This is a MS SQL Server site)

  Is the combination of TO_CHAR(WHEN_OPENED,'MONTH,YYYY'), OID_silverpop_open and site_id unique perhaps? If so, then the count as grouped by those 3 columns will be only one.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  April 20, 2012 at 6:56 am

  #1476593

  select

  ID,

  SITE_ID,

  TO_CHAR(WHEN_OPENED,'MONTH,YYYY') MONTH_DATE

  COUNT(*) OPENCOUNT,

  from

  OPEN

  where

  WHEN_OPENED is not null and

  TRUNC(O.WHEN_OPENED) between '01-APR-2010'

  and '31-MAR-2012'

  GROUP BY ID, SITE_ID, TO_CHAR(WHEN_OPENED,'MONTH,YYYY')

  If you are attempting to get a count of rows that have the same (ID, SITE_ID) and where opened on the same Month/Year then try the code above.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.