Why does my SUMFUNCTION doesn't work?

Question

Post reply

Why does my SUMFUNCTION doesn't work?

davdam8

Hall of Fame

Points: 3620
More actions
October 25, 2012 at 11:39 am

#269102

Hi geniuses!
Can anybody detect the error here?
The real info is:
NP |:| VT |:| DtL2010 |:| Analisys.PctGUID
A |:| Aa |:| 10000 |:| .........................
B |:| Bb |:| 1200 |:| .....................
C |:| Cc |:| 50000 |:| ........................
So the SUM should give me 61200, completing with it each row of DtL2010 Column, right?
SELECT DISTINCT Analisys.NP as NP, ValueType.Value as VT, SUM(Analisys.Value) AS DtL2010, Analisys.PctGUID
FROM ValueType INNER JOIN
Analisys ON ValueType.ID = Analisys.ValueTypeID
WHERE (ValueType.Value = '03 - DPM') AND (Analisys.Year= '2010')
GROUP BY Analisys.NP, ValueType.Value, Analisys.Value, Analisys.PctGUID
ORDER BY BFMFinantialAnalisys.NomeProjecto
Thanks
Regards

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That's because it's giving your results by groups, not the total.

As you didn't post DDL or consumable sample data, here's an example that can help you to fix the query.

DECLARE @test-2TABLE(

npvarchar(10),

vrvarchar(10),

dtl2010int)

INSERT @test-2 VALUES

( 'A', 'Aa', 10000),

( 'B', 'Bb', 1200),

( 'C', 'Cc', 50000)

SELECT np,

vr,

SUM( dtl2010) OVER()

FROM @test-2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

davdam8 Hall of Fame Points: 3620 More actions · Answer 2

Go easy on those QUERIES, just a rookie here!

Can't figure it out here:

Why SUM of (123812,953300 + 20000,000000 + 370015,120000) result used in the query below = 1151469,099900 !?

SELECT SUM(Value) AS Expr1

FROM FinAnalisys

WHERE (Caps <> '') AND (ValueTypeID = '18') AND (Value <> '0')

Thanks

Regards!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

davdam8 (10/26/2012)
Go easy on those QUERIES, just a rookie here!
Can't figure it out here:
Why SUM of (123812,953300 + 20000,000000 + 370015,120000) result used in the query below = 1151469,099900 !?
SELECT SUM(Value) AS Expr1
FROM FinAnalisys
WHERE (Caps <> '') AND (ValueTypeID = '18') AND (Value <> '0')
Thanks
Regards!

It is not!

Could you please post what the following will return (exactly as returned):

SELECT Value

FROM FinAnalisys

WHERE (Caps <> '') AND (ValueTypeID = '18') AND (Value <> '0')

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

davdam8 Hall of Fame Points: 3620 More actions · Answer 4

It returns 3 values:

370015.120000

20000.000000

123812.953300

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

There really isn't enough information to really provide you with a good answer. Please post the DDL (CREATE TABLE statement) for the table involved in the query.

Also, to better help you, please take the time to read the first article that I have referenced below in my signature block regarding asking for help. If you follow the instructions in that article regarding what you should post and how you should post it, you will get better answers must faster and it will include tested code.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

davdam8 (10/26/2012)
It returns 3 values:
370015.120000
20000.000000
123812.953300

Just three rows?

Or it does return this:

370015.120000

20000.000000

123812.953300

The sum you have mentioned is the SUM of the above sequence.

If the query I've asked you to run returned just three rows, your aggregate query would return correct sum of 513828.073300.

DECLARE @FinAnalisys TABLE (Value DECIMAL(18,6)

,Caps VARCHAR(10)

,ValueTypeId CHAR(2))

insert @FinAnalisys SELECT 370015.120000,'a','18'

insert @FinAnalisys SELECT 20000.000000,'a','18'

insert @FinAnalisys SELECT 123812.953300,'a','18'

SELECT SUM(Value)

FROM @FinAnalisys

WHERE (Caps <> '') AND (ValueTypeID = '18') AND (Value <> '0')

Can you cut-and-paste the exact results of the query execution from SSMS, together with the query without any change?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]