June 22, 2005 at 7:39 am
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
Name nSpeed nTotal nStatus
-------------------------------------------------------
Test 1 12000 30000 1
Test 1 30000 34000 3
Test 2 42000 10000 3
I want the sum to show the following:
Results
Name nSpeed nTotal
--------------------------------------
Test 1 30000 64000
Test 2 42000 10000
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
June 22, 2005 at 7:45 am
Did you mean for your nStatus to be "2" in row 2 ?!
**ASCII stupid question, get a stupid ANSI !!!**
June 22, 2005 at 7:48 am
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
June 22, 2005 at 7:52 am
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
June 22, 2005 at 7:53 am
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
Vasc
June 22, 2005 at 8:05 am
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
June 22, 2005 at 8:09 am
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 : )
Vasc
June 22, 2005 at 8:11 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply