How to encorporate IF

Hot Topic

Can't connect to SQL Server 2005
Editing User Defined Data type
2 independent Column Groups in a matrix?
Help SVP: How to connect to external server and backup db with SSMSExpress
From DataSet To SqlDataReader in a CLR Stored Procedure
Excel headers - using SimplePageHeaders deviceinfo setting
SQL reporting services 2005 with CLR
Problems with using "Patch" in the XML task
Service Manager
Replication from a 2005 database to a 2000 database

SQL Server

Query to Get and Set Image data type Value in SQL Query
TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host
Problem: Deployment works, but processing does not
Are report parameters cached in the report server?
SSIS Requests...
Using SSMS to Manage SSIS
Update column with 8 digit random number
Reference for SSIS "C-like" script needed
Using Forms Authentication in Reporting Services
Join filter failure

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



Answer this question

How to encorporate IF

  • cristof

    SELECT     RMSFILENUM, sum(rmstranamt) rmstranamt10
    FROM RFINANL
    WHERE RMSTRANCDE = '10'
    GROUP BY RMSFILENUM

  • cthistle

    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



  • Fabio Pirani

    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



  • theinspector

    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

  • CurtGibson

    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

  • Anand Raman - MSFT

    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

  • ZeR_o

    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
  • How to encorporate IF
Answer this question