SUM Not working

Question

Post reply

SUM Not working

dw-386578

SSC Veteran

Points: 224
More actions
January 26, 2007 at 9:13 am

#171940

Can anyone help with this one?
I am doing a straitforward sum on a table with character, integer and money type columns (see code), but it does not aggregrate (sum) the rows. Any ideas?
SELECT TOP 100 PERCENT
CustomerID, SlsRep, Season,
Division, StyleNum, ColorNum,
cast(NULL as char(3)) AS F06Flag,
cast(NULL as char(3)) AS S07Flag,
cast('F07' as char(3)) AS F07Flag,
SUM(FcstQty) AS FcstQty, SUM(WholesaleDollars) AS WholesaleDollars,
@CrntSubFcst AS CrntSubFcst ,
SUM(SubFcst1Qty)AS SubFcst1Qty, SUM(SubFcst1WholesaleDollars)AS SubFcst1WholesaleDollars,
SUM(SubFcst2Qty)AS SubFcst2Qty, SUM(SubFcst2WholesaleDollars)AS SubFcst2WholesaleDollars,
SUM(SubFcst3Qty)AS SubFcst3Qty, SUM(SubFcst3WholesaleDollars)AS SubFcst3WholesaleDollars
INTO dbo.F07FcstSlsRepDomesticTbl
FROM dbo.F07FcstSlsRepDomesticTbl_tmp
GROUP BY
CustomerID, SlsRep, Season,
Division, StyleNum, ColorNum, CrntSubFcst,F06Flag,
S07Flag, F07Flag
ORDER BY
CustomerID, SlsRep, Season,
Division, StyleNum, ColorNum

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Can you post the DDL of the table involved and some sample data?

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>but it does not aggregrate (sum) the rows

Do the columns being SUM()'ed allow NULL ? Summing a NULL value along with other values always returns NULL as the SUM() result.

Also, what's with the TOP 100 PERCENT and ORDER BY ?

Is this being used to force an ORDER BY in a view ? If so, be aware that this is a 'hack', and is not supported in SQL2K5 and beyond.

[Edit] Oops, I see it's a SELECT .. INTO, not a view. What are you hoping to achieve by ordering results INTO a new table ? The ordering does not guarantee anything about the physical row ordering in the destination table.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

"Do the columns being SUM()'ed allow NULL ? Summing a NULL value along with other values always returns NULL as the SUM() result."

That's not true. SUM() and most other aggregate functions discard NULLs during the aggregation process. The exception to this rule is COUNT(*) which counts all rows, whether they are NULL or not.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

Doh. You are correct of course.

Mental note to self: no forum postings before 1st coffee of the day.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

Your table F07FcstSlsRepDomesticTbl_tmp may be haing these columns.

CrntSubFcst,F06Flag, S07Flag, F07Flag

Avoide these in group by and you should be okay.

Regards,
gova

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

If you try this

create table t(i int)

insert into t values(null)

insert into t values(1)

select sum(i) from t

You get 1.