MS SQL and conditionalAggregate

[rmatthew]

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

[hog]

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

 

don't use the query analyzer as it's 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!

[wyrd]

don't use the query analyzer as it's crap!

 

Silliest thing I've heard in a while..

 

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

 

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

 

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. It's at least worth a shot as it'd simplify your SQL command quite a bit if it does work.

[hog]

 

Silliest thing I've heard in a while..

 

 

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

 

I'm 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 :-((

[Nerseus]

You can use IIF in Access (Jet maybe?).

 

I'd use two subselects, if possible. Try something like:

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 doesn't work let me know. I can try to work it out for real.

 

-Nerseus

[wyrd]

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

 

I wasn't being defensive, I was voicing my opinion. :) I admit maybe I worded it a bit rudely, but it wasn't intended as such.

Of course, if you really want, we could get medieval *busts out shield and sworld* :D

[hog]

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 doesn't mean I'm preposing :-))))