Simple (complicated to me) SQL Query with SUM

[Fentontech]

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?

[BitBlt]

Try this:

select 
propno, 
bldgno, 
unitno, 
convert(varchar(12), leasestartdate, 101) as leasestartdate, 
convert(varchar(12), effectivedate, 101) as effectivedate, 
sum(annualamount) 
from mpw1400 a
inner join mpw1482 b
on a.leaserecno = b.leaserecno
where b.certno = 1
group by 
propno, 
bldgno, 
unitno, 
convert(varchar(12), leasestartdate, 101), 
convert(varchar(12), effectivedate, 101)

It should do the trick.

[Fentontech]

I get an err "The second parameter LeaseStartDate for CONVERT is invalid." with that query....

[BitBlt]

Might be because of the way the column datatype is defined. In my test, it was a datetime datatype. If yours is a varchar, just don't use the "convert" function in either the "select" clause or the "group by" clause

[Fentontech]

I may be doing something wrong still, this is what I have for the query,

SELECT 
	PropNo, 
	BldgNo, 
	UnitNo, 
	LastName,
	FirstName,
	LeaseStartDate, 
	EffectiveDate, 
	sum(AnnualAmount) AS TotalAmount
FROM 
	MPW1400 a
INNER JOIN 
	mpw1482 b
ON 
	a.LeaseRecNo = b.LeaseRecNo
WHERE 
	b.CertNo = 1
GROUP BY 
	PropNo, 
	BldgNo, 
	UnitNo, 
	LastName,
	FirstName,
	LeaseStartDate,
	EffectiveDate
ORDER BY PropNo,BldgNo,UnitNo ASC

And this is what I get for a result;

WED69     46        1136      Scruggs                                          Maissha                  2/11/2009     2/4/2009                   2088.0
WED69     47        1116      Hudgens                                          Baletha                   3/9/2007    1/31/2007                  2947.88
WED69     47        1116      Hudgens                                          Baletha                   3/9/2007     2/2/2007                   4218.0
WED69     47        1116      Jefferson                                        Matika                   5/14/2007    5/14/2007                  20404.6
WED69     47        1122      Fakir                                            Fatima                   11/1/2005    11/1/2005                   2484.0
WED69     48        1102      Brown                                            Charinease               11/1/2005    11/1/2005                  15070.0
WED69     48        1102      Craig                                            Felicia                  9/22/2008    8/28/2008                   2088.0
WED69     48        1102      Craig                                            Felicia                  9/22/2008    8/29/2008                   5004.0
WED69     48        1106      Bradley                                          Folonda                  11/1/2005    11/1/2005                  12000.0
WED69     49        1103      Dunbar                                           Terone                  12/21/2009    9/23/2009                   9334.0
WED69     49        1103      Johnson                                          Jameela                   2/1/2006     2/1/2006                  11400.0
WED69     49        1103      Renfro                                           Reginald                 6/15/2007    6/11/2007                  15600.0
WED69     49        1103      Renfro                                           Reginald                 6/15/2007    6/14/2007                   6000.0
WED69     49        1105      Evans                                            Dione                     6/1/2006     6/1/2006                  14560.0

Notice the records that arent get totaled...maybe because the have different cert dates?

[Fentontech]

I know in my example I showed all the Effective dates as the same but they are different even with the same CertNo

I appreciate you helping figure this out!

[BitBlt]

Is cert date the same as EffectiveDate? If so, yes you are correct. If you remove "EffectiveDate" from the grouping and the column list, then it will total.

[Fentontech]

THATS AWESOME! but how do I display the earliest Effective Date in the same query?

[BitBlt]

Just include "min(effectivedate) as MinEffDate" before the sum(annualamount)

That should do the trick.

[Fentontech]

Perfect! here is my final code if anyone is interested.....

SELECT 
	PropNo, 
	BldgNo, 
	UnitNo, 
	LastName,
	FirstName,
	LeaseStartDate,
	MIN(EffectiveDate) AS CertDate,
	sum(AnnualAmount) AS TotalAmount
FROM 
	MPW1400 a
INNER JOIN 
	mpw1482 b
ON 
	a.LeaseRecNo = b.LeaseRecNo
WHERE 
	b.CertNo = '1'
GROUP BY 
	PropNo, 
	BldgNo, 
	UnitNo, 
	LastName,
	FirstName,
	LeaseStartDate
ORDER BY 
	PropNo,
	BldgNo,
	UnitNo 
		ASC