MS SQL and conditionalAggregate

R

rmatthew

Well-known member
Joined
Dec 30, 2002
Messages
115
Location
Texas
I am trying to use the following syntax:

select machid,sum(iif(pvalue > 0,pvalue,0)) ,sum(iif(pvalue<0,pvalue,0)) from valuetable group by machid

esentiallly I have a table that has a field that I need to add all of the . values and all of the neg. values seperately to return two distinct calculations.

any ideas - query analyzer returns "Incorrect syntax near >."

Thanks in Advance
 
A tip passed onto to me by this forum a while ago....

dont use the query analyzer as its crap!

I have found it to report syntax errors on perfectly good sql.

result.......write your sql manually to save a whole lot of grief!
 
dont use the query analyzer as its crap!
Click to expand...

Silliest thing Ive heard in a while..

select machid,sum(iif(pvalue > 0,pvalue,0)) ,sum(iif(pvalue<0,pvalue,0)) from valuetable group by machid
Click to expand...

Strange syntax, does SQL Server support iif? Looks like a VB thing. Try using CASE or just a regular IF statement;

Code: 
SELECT
   machid,
   SUM(CASE WHEN pvalue > 0 THEN pvalue ELSE 0),
   SUM(CASE WHEN pvalue < 0 THEN pvalue ELSE 0)
FROM valuetable
   GROUP BY machid

As a side though, have you tried SUM(pvalue > 0), SUM(pvalue < 0)? SUM() accepts an expression, so it might work. Its at least worth a shot as itd simplify your SQL command quite a bit if it does work.
 
Silliest thing Ive heard in a while..
Click to expand...

Is it me wyrd but you seem to always be on the defensive re anyones comments?

Im only relaying what Derek Stone pointed out to me some time ago which is totally true. The analyer does stuff up simple legit sql simple as that!

See my post :

This ole SQL chestnut....still :-((
 
You can use IIF in Access (Jet maybe?).

Id use two subselects, if possible. Try something like:
Code: 
select v1.machid, sum(v1.pvalue), sum(v2.pvalue) from valuetable v1 INNER JOIN valuetable v2 ON v1.machid = v2.machid group by v1.machid

If that doesnt work let me know. I can try to work it out for real.

-Nerseus
 
Is it me wyrd but you seem to always be on the defensive re anyones comments?
Click to expand...

I wasnt being defensive, I was voicing my opinion. :) I admit maybe I worded it a bit rudely, but it wasnt intended as such.
Of course, if you really want, we could get medieval *busts out shield and sworld* :D
 
Nah....no worries wyrd...I think my reply came across rude also.

Tell ya what lets forget all about it and remain friends.....howver this doesnt mean Im preposing :-))))
 
Back
Top