How to encorporate IF

Question

Post reply

How to encorporate IF

dba321

Hall of Fame

Points: 3917
More actions
January 11, 2006 at 1:27 pm

#167738

How can I put an If statement saying If # RECORDS returned from the query below
IF # records returned is > 1 then
SUM(rmstranamt) AS rmstranamt10
ELSE
rmstranamt AS rmstranamt10

here's my statement
-------------------------
SELECT RMSFILENUM,
rmstranamt AS rmstranamt10 <-----If statement goes here base on if the amount of records found in select is >1 or not
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

I want to return sum(rmstranamt) AS rmstranamt10 in cases where the same query produces > 1 records
In other words if this:
SELECT RMSFILENUM,
rmstranamt AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
produces > 1 records, then I don't want to return rmstranamt AS rmstranamt10, I want to return sum(rmstranamt) AS rmstranamt10

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

If number of records = 1 then sum(rmstranamt) = rmstranamt.

So just use sum(rmstranamt).

_____________
Code for TallyGenerator

0tter SSC Veteran Points: 280 More actions · Answer 2

try this in place of your code.

SELECT RMSFILENUM,

sum(rmstranamt) AS rmstranamt10 1 or not

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 3

Hi, I can´t understand what are you trying to do... if your query returns just a single record sum(x) = x because there is JUST ONE record... so, you doesn´t need to check for the number of records... also, to return just de field and not the sum, you have to put it in the group clause...

Checking conditions into a query is well done using CASE clause... see BOL for info on that.

Good Luck

Nicolas Donadio

Software Team Leader

Mitrol SRL

Argentina

gbn SSCertifiable Points: 6429 More actions · Answer 4

I'm also not sure what you are trying to do but if you have several records where RMSTRANCDE = '10' and you want to return 0 for zero records and return 1 for more than 1 record then try this

SELECT

RMSFILENUM,

CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

David Burrows SSC Guru Points: 65142 More actions · Answer 5

SELECT RMSFILENUM, SUM(rmstranamt) AS [rmstranamt10]

FROM (SELECT RMSFILENUM, rmstranamt AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM, rmstranamt) x

GROUP BY RMSFILENUM

Far away is close at hand in the images of elsewhere.
Anon.

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

For my money I'd go with Sergiy - why bother to count # of rows - just sum(rmstranamt) should be adequate...

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

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Unless it it the sum of unique rmstranamt's that is required

Far away is close at hand in the images of elsewhere.
Anon.