Tosh Tosh - 1 year ago 56
SQL Question

Group Data Within Query

I wrote this query and the results are not being grouped. They are appearing as such:

Facility | Posting Month | Account Type | Amount
Name | July | Debit Adj. | 100
Name | July | Credit Adj. | -200
Name | July | Debit Adj. | 150
Name | July | Credit Adj. | -150

The results I am trying to get is the Posting month to appear once for the Debit Adj. and Credit Adj. type and the respective total amounts. Total of $250 for Debits and -$350 for credits. Thanks for the help!

SELECT pw.Facility, DATENAME(mm, pw.[Posting Date]) AS [Posting Month],
lc.[Gl Account Type], sum(pw.[Tx Amt]) AS Amount
FROM DBO.PaymentWOLedger AS pw
JOIN DBO.LedgerCodeTable AS lc
ON lc.[Description] = pw.[Tx Desc]
WHERE lc.[Gl Account Type] = 'Credit Adjustment (Write Off)' OR
lc.[GlAccount Type] = 'Debit Adjustment (CWO)'
GROUP BY pw.[Posting Date], pw.Facility, lc.[Gl Account Type]
ORDER BY pw.Facility, pw.[Posting Date]

Answer Source

Use Month instead of Date in Group by & Order by

GROUP BY DATENAME(mm, pw.[Posting Date]), pw.Facility, lc.[Gl Account Type]
ORDER BY pw.Facility, DATENAME(mm, pw.[Posting Date])

currently the data is grouped on each date since you have used pw.[Posting Date] in Group by which has date/month/year part in it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download