Summing up Specific Values

Question

Post reply

Summing up Specific Values

naylor67

Old Hand

Points: 369
More actions
June 22, 2005 at 7:39 am

#164132

Group:
I have a query that is summing up two values (nSpeed and nTotal), but I want the query to not include the nSpeed value of the record if its nStatus is not equal to 3.
Sample data:
Entire Table
NamenSpeednTotalnStatus
-------------------------------------------------------
Test 1 1200030000 1
Test 13000034000 3
Test 24200010000 3
I want the sum to show the following:
Results
NamenSpeednTotal
--------------------------------------
Test 1 3000064000
Test 24200010000
So, I do not want the nSpeed of 12000 to be included in the summation, but I do want the nTotal to be summed up. Any ideas?
Regards,
Bob

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

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Did you mean for your nStatus to be "2" in row 2 ?!

**ASCII stupid question, get a stupid ANSI !!!**

naylor67 Old Hand Points: 369 More actions · Answer 2

No....nStatus values are as follows:

is if it is active nStatus = 3

if it is in maintenance nStatus = 1

So i do not want to include the nStatus = 1 in the speed summation, but do want the nTotal to be included. I am grouping them based on Product Name.

Regards

Bob

naylor67 Old Hand Points: 369 More actions · Answer 3

here is what i have so far:

select p.name,

sum(nSpeed), -- just do not sum up the nSpeed for those records with nStatus = 1

sum(nLineTotal)

from statusTable st join product p ON

st.pdtno = p.pdtno

group by p.name,

st.publyear,

st.issord,

st.pdtrnno

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 4

select p.name,

sum(CASE WHEN nStatus=1 0 else nSpeed end ),sum(nLineTotal)

from statusTable st join product p ON

st.pdtno = p.pdtno

group by p.name,

st.publyear,

st.issord,

st.pdtrnno

Kindest Regards,

Vasc

SJTerrill SSCrazy Points: 2938 More actions · Answer 5

If I understand the request correctly, Vasc, I think he wants it t'other way 'round:

SELECT ThisName,

Sum(CASE WHEN nStatus = 3 THEN nSpeed ELSE 0 END),

Sum(nTotal)

...

--SJT

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 6

Maybe I just looked at his last post :

-- just do not sum up the nSpeed for those records with nStatus = 1

which is contractidory to what he sais at the begining : )

but I want the query to not include the nSpeed value of the record if its nStatus is not equal to 3.

One of the SOL will work for him : )

Kindest Regards,

Vasc

naylor67 Old Hand Points: 369 More actions · Answer 7

Sorry for the confusion everyone...Vasc, your last post is the way I wanted it. I apologize.

Thank you everyone. I believe this is going to work for me.

Much appreciated.

Regards

Bob