need some group by multiple rows help

• chukostar

  Old Hand

  Points: 331

  More actions

  July 15, 2011 at 11:11 am

  #253778

  Hi,

  My group by query returns this (based on my table with mulit columns and rows with 0 values):

  AB003

  AB080

  AB011

  AB23023

  BC0020

  BC090

  BC35035

  MB010

  MB909

  ...

  how do I eliminate the zeros and sum them up like this:

  AB23927

  BC35955

  MB919

  already tried using 'having ... >0 or ... > 0 or ... > 0' didn't help 🙁

  thanks in advance

• bopeavy

  SSCertifiable

  Points: 5869

  More actions

  July 15, 2011 at 11:21 am

  #1353577

  Based on what you gave :

  CREATE TABLE #MySample

  (

  ID varchar(15)not null,

  col1 int,

  col2 int,

  col3 int

  )

  INSERT INTO #MySample VALUES ('AB', 0, 0, 3)

  INSERT INTO #MySample VALUES ('AB', 0 ,8 ,0)

  INSERT INTO #MySample VALUES ('AB', 0 ,1 ,1)

  INSERT INTO #MySample VALUES ('AB', 23 ,0 ,23)

  INSERT INTO #MySample VALUES ('BC', 0 ,0 ,20)

  INSERT INTO #MySample VALUES ('BC', 0, 9, 0)

  INSERT INTO #MySample VALUES ('BC', 35, 0 ,35)

  INSERT INTO #MySample VALUES ('MB',0 ,1 ,0)

  INSERT INTO #MySample VALUES ('MB', 9 ,0, 9)

  -- this will give you the results.

  SELECT ID, SUM(col1),SUM(col2),SUM(col3) from #MySample

  group by ID

  :cool:

  
  

  "There are no problems! Only solutions that have yet to be discovered!"

• chukostar

  Old Hand

  Points: 331

  More actions

  July 15, 2011 at 11:26 am

  #1353582

  it works 🙂 thanks,

• bopeavy

  SSCertifiable

  Points: 5869

  More actions

  July 15, 2011 at 11:29 am

  #1353584

  No problem, gald to help!

  :cool:

  
  

  "There are no problems! Only solutions that have yet to be discovered!"