ApexCo
asked on
Totaling/Grouping in SQL query
My data returned looks like this:
Description StatuteReference Page Sort Order GLAccountId Total
ClientX 4875 1 1215 10 3752.84
ClientX 4875 1 1215 11 3425.84
ClientX 4875 1 1215 12 2134.84
How would I change my query to have these merged into a single record and have a "total" on the Total column above?
Thanks.
Description StatuteReference Page Sort Order GLAccountId Total
ClientX 4875 1 1215 10 3752.84
ClientX 4875 1 1215 11 3425.84
ClientX 4875 1 1215 12 2134.84
How would I change my query to have these merged into a single record and have a "total" on the Total column above?
Thanks.
SELECT
str.Description, str.StatuteReference, str.Page, str.SortOrder
,STRA.SourceCodeId
,GLT.GLAccountId, GLT.Amount
FROM
rp.StateRemittance as STR
INNER JOIN rp.StateRemittanceAccount STRA on str.StateRemittanceId = STRA.StateRemittanceId
INNER JOIN
(SELECT
GlAccountId, PostingDate, Amount
FROM
ac.GLTransaction
GROUP BY
GlAccountId, PostingDate, Amount) GLT
ON
STRA.GLAccountId = GLT.GLAccountId
WHERE GLT.PostingDate BETWEEN '3/01/2010' AND '3/31/2010'
ORDER BY SortOrder ASC
ASKER
I'm looking to really group this by the Description field. All of the clients needs to have their totals summed up.
Even if the GLAccount Id is different. Still possible to do?
Even if the GLAccount Id is different. Still possible to do?
OK, then we'll just grab the minimum GLAccountID and sum the Total, grouping by Description.
Please try:
Please try:
SELECT
str.Description, min(str.StatuteReference) as StatuteReference, min(str.Page) as Page, str.SortOrder
,STRA.SourceCodeId
,min(GLT.GLAccountId) as GLAccountID, Sum(GLT.Amount) as Total
FROM
rp.StateRemittance as STR
INNER JOIN rp.StateRemittanceAccount STRA on str.StateRemittanceId = STRA.StateRemittanceId
INNER JOIN
(SELECT
GlAccountId, PostingDate, Amount
FROM
ac.GLTransaction
GROUP BY
GlAccountId, PostingDate, Amount) GLT
ON
STRA.GLAccountId = GLT.GLAccountId
WHERE GLT.PostingDate BETWEEN '3/01/2010' AND '3/31/2010'
Group By str.Description, str.SortOrder ,STRA.SourceCodeId
ORDER BY SortOrder ASC
ASKER
That's a definite improvement.
However, the "Total" column has the same value for every record.
However, the "Total" column has the same value for every record.
I expected only a single record to be returned in place of the 3 you posted.
Would you post the result of my query?
Would you post the result of my query?
ASKER
Description StatuteReference Page SortOrder SourceCodeId Total
Victim Rights-Juvenile 08-418;41-191-08 1 32 1114 4895.58
Description StatuteReference Page SortOrder SourceCodeId Total
JCEF-Time Payment 12-113G;12-116B 1 60 309 4895.58
Description StatuteReference Page SortOrder SourceCodeId Total
JCEF-Probation Surcharge 12-114.01 1 65 375 4895.58
Those are 3 different records from the result set. You can see everything looks good, except the total.
Victim Rights-Juvenile 08-418;41-191-08 1 32 1114 4895.58
Description StatuteReference Page SortOrder SourceCodeId Total
JCEF-Time Payment 12-113G;12-116B 1 60 309 4895.58
Description StatuteReference Page SortOrder SourceCodeId Total
JCEF-Probation Surcharge 12-114.01 1 65 375 4895.58
Those are 3 different records from the result set. You can see everything looks good, except the total.
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 for the help, you put me on the right path.
Final result:
Final result:
SELECT str.DESCRIPTION,
MIN(str.statutereference) AS statutereference,
MIN(str.page) AS page,
str.sortorder,
SUM(glt.amount) AS total
FROM rp.stateremittance AS str
INNER JOIN rp.stateremittanceaccount stra
ON str.stateremittanceid = stra.stateremittanceid
INNER JOIN ac.gltransaction glt
ON stra.glaccountid = glt.glaccountid
AND stra.sourcecodeid = glt.sourcecodeid
WHERE glt.postingdate BETWEEN '1/01/2010' AND '1/31/2010'
GROUP BY str.DESCRIPTION,
str.sortorder
ORDER BY sortorder ASC
If you want to pick the minimum or the maximum, a SUM operation becomes easy.
Or are you looking to have each GLAccountID in its own column? That gets into PIVOT operations ...