Summing by first 7 records, then grouping?

Hello. I've got the following query and it returns 13 records, basically monthly payments. How can I sum the first 7 and the last 6 records and then group accordingly? And just to clarify, summing on first 7, then last 6, and then having 2 groups. 1st group has the first 7, 2nd group has the last 6.

Here is some sample data.

TaxesDue      TaxesPaid      Year
0      NULL      2009
3.16      NULL      2009
3.62      NULL      2009
5.54      NULL      2009
7.02      NULL      2009
7.22      NULL      2009
12.26      NULL      2009
23.86      NULL      2009
60.38      NULL      2009
105.66      NULL      2009
121.86      NULL      2009
211.86      NULL      2009
212.5      NULL      2009
233.28      NULL      2009



SELECT

BKT.DebtorName,
BKT.CaseNumber,
BKT.DischargeDate,
bkt.ChapterId,
PAR.ParcelNumber,
att.FullName as AttorneyName,
att.Phone as AttorneyPhone,
att.Address1,
att.City,
att.[state],
att.Zip,
PAT.TaxAmount as TaxesDue,
SUM(PMT.PaymentAmount) as TaxesPaid,
PCI.BeginParcel,
bkt.ProtectedThruYear,
PAT.[Year]



FROM tx.Bankruptcy BKT
LEFT JOIN xt.AddressBook ATT on BKT.AttorneyAddressBookId = ATT.AddressBookId 
LEFT JOIN tx.ParcelControlGroup PCG on BKT.ParcelControlGroupId = PCG.ParcelControlGroupId
LEFT JOIN tx.ParcelControlItem PCI on PCG.ParcelControlGroupId = PCI.ParcelControlGroupId
LEFT JOIN tx.Parcel PAR on PCI.BeginParcel = PAR.ParcelNumber
LEFT JOIN tx.ParcelAuthTaxes PAT on PAR.ParcelId = PAT.ParcelId 
LEFT JOIN tx.Payment PMT on Par.ParcelId = PMT.ParcelId AND PAT.[Year] = PMT.[Year]

WHERE  BKT.CaseNumber = '05-01115-JMM-AZ' AND PAT.[Year] > ProtectedThruYear AND PaymentAmount is null

Group by PAT.[year],bkt.ProtectedThruYear,
PMT.PaymentAmount,
BKT.DebtorName,
BKT.CaseNumber,
BKT.DischargeDate,
bkt.ChapterId,
PAR.ParcelNumber,
att.FullName,
att.Phone,
att.Address1,
att.address2,
att.City,
att.[state],
PCI.BeginParcel,
PAT.TaxAmount,
att.Zip

Open in new window

LVL 8
ApexCoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ApexCoAuthor Commented:
I'd like to add something to this.
It is entirely possible I will be returning more than a single year. So I would need the group by year to still apply, then sum within each year as described above.
So first 7 then last 6 for 2008, 2009, 2010  etc.
 
Thanks!
0
Paul_Harris_FusionCommented:
Select GRP, sum(TaxesDue)
from
(
Select *,  CAST((RN-1) / 7 as integer) as GRP
from
(
select *, row_number() OVER (ORDER BY Year) as RN
from MyTable
) q1
) q2
GROUP BY GRP
0
ApexCoAuthor Commented:
Hi Paul,
I'm a little confused on how to apply that to my current SQL query.
0
Paul_Harris_FusionCommented:
take my central query
  select *, row_number() OVER (ORDER BY Year) as RN
  from MyTable

and replace it with your query but add the RN column.

Replace the top line of my query with the summary expressions of your choice

For clarity and isolation, you could create a view from your query
i.e.
CREATE VIEW tempView as
(
YourSQL
)

then use

Select GRP,  sum(TaxesDue)  -- replace with your own column names
from
(
Select *,  CAST((RN-1) / 7 as integer) as GRP
from
(
select *, row_number() OVER (ORDER BY Year) as RN
from tempView
) q1
) q2
GROUP BY GRP
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ApexCoAuthor Commented:
Thanks Paul, works fantastic.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.