Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Simple (complicated to me) SQL Query with SUM

    I have 2 tables,

    MPW1400
    LeaseRecNo
    PropNo
    BldgNo
    UnitNo
    LeaseStartDate

    MPW1482
    LeaseRecNo
    EffectiveDate
    AnnualAmount
    CertNo


    My Objective is to get the total of 'AnnualAmount' Where MPW1400.LeaseRecNo = MPW1482.LeaseRecNo and CertNo = '1'

    Example:

    MPW1400
    -------------------------------------------------------
    LeaseRecNo | PropNo | BldgNo | UnitNo | LeaseStartDate
    -------------------------------------------------------
    12345 | RG52 | 24 | 5A | 12/25/2008
    12346 | RG52 | 24 | 5B | 01/22/2007
    12347 | RG52 | 24 | 3A | 08/05/2004
    12348 | RG52 | 24 | 2A | 10/19/2008
    12349 | RG52 | 24 | 1A | 11/16/2006
    12350 | RG52 | 24 | 1B | 04/09/2001
    -------------------------------------------------------


    MPW1482
    -------------------------------------------------------
    LeaseRecNo | CertNo | AnnualAmount | EffectiveDate
    -------------------------------------------------------
    12345 | 1 | 3015. | 12/25/2008
    12345 | 1 | 400.45 | 12/25/2008
    12345 | 1 | 6000. | 12/25/2008
    12345 | 2 | 1800. | 12/25/2008
    12345 | 3 | 24000. | 12/25/2008
    12345 | 1 | 9000. | 12/25/2008
    -------------------------------------------------------


    So the Desired Query would result in the following output;

    PropNo | BldgNo | UnitNo | LeaseStartDate | EffectiveDate | AnnualAmount
    -------------------------------------------------------------------------
    RG52 | 24 | 5A | 12/25/2008 | 12/25/2008 | 18415.45


    I have put together many different combinations of select statemets and subselects to only get mixed results non of which is my desired result.... Help?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    FentoTech, based on the sample data you posted, this should get you what you want. I am not at a place where I can test it, however.
    Code:
    SELECT MPW1400.PROPNO
         , MPW1400.BLDGNO
         , MPW1400.UNITNO
         , MPW1400.LEASESTARTDATE
         , MPW1482.EFFECTIVEDATE
         , SUM(MPW1482.ANNUALAMOUNT) AS ANNUALAMMOUNT
    FROM MPW1400
           INNER JOIN
         MPW1482
           ON     MPW1400.LEASERECNO = MPW1482.LEASERECNO
              AND MPW1482.CERTNO     = 1
    GROUP BY MPW1400.PROPNO
           , MPW1400.BLDGNO
           , MPW1400.UNITNO
           , MPW1400.LEASESTARTDATE
           , MPW1482.EFFECTIVEDATE
    It does assume that Effective Date is the same for all rows in MPW1482. If they are not, you need to determine which value you want (min, max, other?)

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    Ya But....

    Quote Originally Posted by Stealth_DBA View Post
    FentoTech, based on the sample data you posted, this should get you what you want. I am not at a place where I can test it, however.
    Code:
    SELECT MPW1400.PROPNO
         , MPW1400.BLDGNO
         , MPW1400.UNITNO
         , MPW1400.LEASESTARTDATE
         , MPW1482.EFFECTIVEDATE
         , SUM(MPW1482.ANNUALAMOUNT) AS ANNUALAMMOUNT
    FROM MPW1400
           INNER JOIN
         MPW1482
           ON     MPW1400.LEASERECNO = MPW1482.LEASERECNO
              AND MPW1482.CERTNO     = 1
    GROUP BY MPW1400.PROPNO
           , MPW1400.BLDGNO
           , MPW1400.UNITNO
           , MPW1400.LEASESTARTDATE
           , MPW1482.EFFECTIVEDATE
    It does assume that Effective Date is the same for all rows in MPW1482. If they are not, you need to determine which value you want (min, max, other?)
    That works but as you suspected the EffectiveDate's can differ, I guess key here for me is to get a single row result based on leaserecno and certno totaling annualamount....

    so close.....

  4. #4
    Join Date
    Sep 2010
    Posts
    153

    Simple one line query would be

    select max(propno) as propno, max(bldgno) as bldgno, max(unitno) as unitno, max(leasestartdate) as leasestartdate, max(effectivedate) as effectivedate, sum(annualamount) as annualamount from mpw1 inner join mpwdemo on mpw1.leaserecno = mpwdemo.leaserecno group by mpw1.leaserecno;






    Here max() doesnt means we are taking the maximum value always, it also means taking one value if there are many. Like three same emp
    _no are there so if we take max(), it helps us to take one value..

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Fentontech View Post
    That works but as you suspected the EffectiveDate's can differ, I guess key here for me is to get a single row result based on leaserecno and certno totaling annualamount....
    This indicates that you want to show (at least) one column too much. When the EffectiveDate's can differ, you shouldn't include that column in your SELECT list.

    You want the ANNUALAMOUNT, so it would be better to change EffectiveDateto to YEAR(EffectiveDate) in the SELECT and the GROUP BY clauses.

    The same will hold for LEASESTARTDATE. I don't know the business rules behind these two columns, you may have to drop EffectiveDate altogether and use YEAR(LEASESTARTDATE). That is up to you to decide.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •