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
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
