Group By totals

[Syerston]

Hi everybody

Would you have a look at the following snippet of code.

SELECT n.ProviderID, n.SchemeID, Sum(Total)
FROM NormalPaymentSchedule n
INNER JOIN ClaimantDetailsTest c
ON c.ClaimantID = n.ClaimantID
WHERE n.Period = 1 AND n.YearNo = 1
GROUP BY n.ProviderID, n.SchemeID, c.ProtectedCase, c.PublicHB

I am trying to get two totals for each scheme, the normal total and the combined total for protected and public cases. However, I can only get it to give me separate totals for protected and public

Many Thanks John

 

[SemperFiDownUnda]

having columns in your GROUP BY clause that are not in your SELECT CLAUSE is not normal as it causes you to get multiple rows that can not be distiguished between ie in your case multiple sums for a single ProverID and SchemeID pair.

But that isn't your question. If you want other agrogated functions just put them in this can include something like this
SELECT n.ProviderID, n.SchemeID, Sum(Total), Sum(ProtecteTotal), Sum(PublicTotal), Sum(ProtecteTotal), Sum(PublicTotal) AS BothTotals

If this is not what you are asking I'll need more info

 

[Syerston]

Thanks for replying.

The table only has one total column for all records. My problem is to sum this column, for each provider and scheme, whether each record is marked as a protected or public case (both separate fields), or as a normal case where both protected and public set to zero. John

 

[SemperFiDownUnda]

AH!

Look up the ROLLUP Keyword. What this does is gives you a total at each level of your group by


SELECT n.ProviderID,
n.SchemeID,
c.ProtectedCase,
c.PublicHB,
Sum(Total)
FROM NormalPaymentSchedule n
INNER JOIN ClaimantDetailsTest c
ON c.ClaimantID = n.ClaimantID
WHERE n.Period = 1 AND n.YearNo = 1
GROUP BY n.ProviderID,
n.SchemeID,
c.ProtectedCase,
c.PublicHB WITH ROLLUP

 

[Syerston]

This appears to bring back each record with a roll up total at the end. But even so I need to clump together the group totals for protected and public not bring them back separately. John

 

[SemperFiDownUnda]

OK Let me explain
GROUP BY GROUPS related records into 1

So If you have a GROUP BY clause with your 4 column and those four column values go down to 1 record then your group by is useless without a ROLLUP keyword

I'm going to use the PUBS database as an example because everyone can get it and try this code

Take this statement
SELECT S.Stor_ID, S.Ord_Date, S.PayTerms, SUM(S.QTY * T.Price) AS TotalAmount
FROM Sales S
INNER JOIN Titles T
ON S.Title_ID = T.Title_ID
GROUP BY S.Stor_ID, S.Ord_Date, S.PayTerms WITH ROLLUP

lets look at the data returned

Stor_ID Ord_Date                    PayTerms     TotalAmount           
------- --------------------------- ------------ --------------------- 
6380    1994-09-13 00:00:00.000     Net 60       32.8500
6380    1994-09-13 00:00:00.000     NULL         32.8500
6380    1994-09-14 00:00:00.000     Net 60       99.9500
6380    1994-09-14 00:00:00.000     NULL         99.9500
6380    NULL                        NULL         132.8000

6380 NULL NULL 132.8000
represents the total of sales for Store 6380 (indicated by all other columns in the GROUP BY having a NULL Value)

6380 1994-09-14 00:00:00.000 NULL 99.9500
represent the total of all sales for store 6380 on the 14th of Sept 94 represented by the NULL only in the last column of the GROUP BY
So you get a TOTAL for each level in your group by, This is like groups within groups

Perhaps you should show us a small sample of data and show a small sample of what you want back from your query