# Thread: Simple (complicated to me) SQL Query with SUM

1. Registered User
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. Registered User
Join Date
May 2009
Posts
509
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. Registered User
Join Date
Mar 2011
Posts
2

## Ya But....

Originally Posted by Stealth_DBA
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. Registered User
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. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by Fentontech
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.