Summarizing data and null columns

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 23, 2003 at 3:33 pm

  #81852

  It's been a long day, and I'm drawing a blank on how to get the data I'm after.

  I've got a table that has a column called "countedby", and it has the business rule on how the data was collected; unfortunately it's sometimes a null value.

  I'm trying to pull together both the nulls and the 1's as the assumed value;

  here's an example:

  --DATA

  CREATE TABLE #FRUIT (COUNTEDBY INT,APPLES INT,ORANGES INT)

  INSERT INTO #FRUIT VALUES (NULL,4,7)

  INSERT INTO #FRUIT VALUES (NULL,5,7)

  INSERT INTO #FRUIT VALUES (1,NULL,3)

  INSERT INTO #FRUIT VALUES (2,4,NULL)

  INSERT INTO #FRUIT VALUES (1,6,7)

  INSERT INTO #FRUIT VALUES (2,7,5)

  SELECT

  ISNULL(COUNTEDBY,1) AS COUNTEDBY,

  SUM(ISNULL(APPLES,0)),

  SUM(ISNULL(ORANGES,0))

  FROM #FRUIT

  GROUP BY COUNTEDBY

  DROP TABLE #FRUIT

  The data returned is similar to this:

  COUNTEDBY

  ----------- ----------- -----------

  1 9 14

  1 6 10

  2 11 5

  In this case, I don't want a row for the Null values, I wanted them to be merged into the values that had the value of 1.

  Could someone point out what I'm missing to get this to merge the nulls and the 1's together?

  lowell@stormrage.com

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• winash

  SSChampion

  Points: 11446

  More actions

  June 23, 2003 at 3:50 pm

  #462710

  How about :

  SELECT

  isNull(COUNTEDBY,1) as Countedby,

  SUM(ISNULL(APPLES,0)),

  SUM(ISNULL(ORANGES,0))

  FROM #FRUIT

  GROUP BY isNull(COUNTEDBY,1)

  this seems to be giving the results that you are looking for...I think....