How to encorporate IF

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

Wouldn't

SUM(rmstranamt)
be the same as
rmstranamt

in the case of one record? Think you should just include the sum and not worry about it.

- Brad
|||

you would think....I've spent 3 days on this...check it out more here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21692997.html

|||I see your issue now, but the problem isn't the SUM function rather the GROUP BY clause.

Just remmove the rmstranamt from your GROUP BY to achieve the desired results, otherwise each row returned represents a SUM applied to a GROUP that has one row.

- Brad|||Brad, you mean remove rmstranamt from my last Group By right? If I do that then It errors out saying that rmstranamt needs to be in it...so what do I do?|||Brad, ok yes, that works but to a certain point. My main problem is when I plug in an account that I know has more than 1 results from my first INNER JOIN query, it throws off my end aggregation and I end up with 2 account rows rather than one for that account|||SELECT RMSFILENUM, sum(rmstranamt) rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM|||

Hi,

SELECT RMSFILENUM
into #tmp

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) <= 1

SELECT RMSFILENUM,
sum(rmstranamt )AS rmstranamt10

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) > 1

union

SELECT r.RMSFILENUM,
rmstranamt AS rmstranamt10

FROM RFINANL r inner join #tmp t on r.RMSFILENUM = t.RMSFILENUM
GROUP BY r.RMSFILENUM, r.rmstranamt

Regards

How to encorporate IF

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

Wouldn't

SUM(rmstranamt)
be the same as
rmstranamt

in the case of one record? Think you should just include the sum and not worry about it.

- Brad
|||

you would think....I've spent 3 days on this...check it out more here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21692997.html

|||I see your issue now, but the problem isn't the SUM function rather the GROUP BY clause.

Just remmove the rmstranamt from your GROUP BY to achieve the desired results, otherwise each row returned represents a SUM applied to a GROUP that has one row.

- Brad|||Brad, you mean remove rmstranamt from my last Group By right? If I do that then It errors out saying that rmstranamt needs to be in it...so what do I do?|||Brad, ok yes, that works but to a certain point. My main problem is when I plug in an account that I know has more than 1 results from my first INNER JOIN query, it throws off my end aggregation and I end up with 2 account rows rather than one for that account|||SELECT RMSFILENUM, sum(rmstranamt) rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM|||

Hi,

SELECT RMSFILENUM
into #tmp

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) <= 1

SELECT RMSFILENUM,
sum(rmstranamt )AS rmstranamt10

FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM having count(rmstranamt) > 1

union

SELECT r.RMSFILENUM,
rmstranamt AS rmstranamt10

FROM RFINANL r inner join #tmp t on r.RMSFILENUM = t.RMSFILENUM
GROUP BY r.RMSFILENUM, r.rmstranamt

Regards

How to encorporate Date and Time comparison to pull a field

I'm trying to pull this data in my first inner join trying to pull the rmsbalance based on the latest date/time combination but not having any luck with my syntax because sql is throwing errors due to my syntax below and I can't figure how how to do the check on the date and time combo:

SELECT rf.RMSTRANCDE,

rm.rmsacctnum,

SUM(rf.rmstranamt) AS [Sum Tran Amt],

rf10.rmsbalance

FROM RMASTER rm

INNER JOIN <-- Problem starts in this inner join

(

SELECT RMSFILENUM,

rmsbalance

FROM RFINANL a

where rmstrandte + rmstrantim = (select max(rmstrandte) + max(rmstrantm) from RFINANL)

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM,

RMSTRANCDE,

SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM, RMSTRANCDE

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

GROUP BY rm.rmsacctnum, rf.RMSTRANCDE, rf10.rmsbalance

HAVING rf10.rmsbalance <> SUM(rf.rmstranamt)

AND rf10.rmsbalance <> 0.00

What is the error message? Syntax of the SELECT statement seems fine.|||

thanks, but I actually figured it out on my own

sql