0

I have a table with the following data

Bldg        Suit    SQFT     Date
1           1       1,000    9/24/2012              
1           1       1,500    12/31/2011
1           2       800      8/31/2012
1           2       500      10/1/2005

I want to write a query that will sum the max date for each suit record, so the desired result would be 1,800, and must be in one cell/row. This will ultimately be part of subquery, I am just not getting what I expect with the queries I have writtren so far.

Thanks in advance.

GSerg
  • 76,472
  • 17
  • 159
  • 346
user1689297
  • 1

2 Answers2

1

You can use the following (See SQL Fiddle with Demo):

select sum(t1.sqft) Total
from yourtable t1
inner join
(
  select max(dt) mxdt, suit, bldg
  from yourtable
  group by suit, bldg
) t2
  on t1.dt = t2.mxdt
  and t1.bldg = t2.bldg
  and t1.suit = t2.suit
Taryn
  • 242,637
  • 56
  • 362
  • 405
0
; With Data As
(
    Select Bldg, Suit, SQFT, Row_Number() Over (Partition By Bldg, Suit Order By Date DESC) As RowID
    From   YourTableNameHere
)
Select Bldg, Sum(SQFT) As TotalSQFT
From   Data
Where  RowId = 1
Group By Bldg
George Mastros
  • 24,112
  • 4
  • 51
  • 59
  • Sorry, I should have specified, I am using T SQL. I am trying to work with both of these, so far, no luck. I will keep at it, thanks for your help! – user1689297 Sep 24 '12 at 18:26