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
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
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
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
ASKER
Hi Paul,
I'm a little confused on how to apply that to my current SQL query.
I'm a little confused on how to apply that to my current SQL query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Paul, works fantastic.
ASKER
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!