CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
user1086159
user1086159

Reputation: 1055

SQL Grouing and sub-totaling

I have the below query:-

SELECT 
FISCALYEAR, 
DATENAME(Month,JVDate) as Month,
ACCOUNTNUMBER,
ACCOUNTDESCRIPTION, 
CATEGORY, 
POSTINGTYPE,
ORIGNETAMOUNT, 
FROM dbo.vw_GLTrialBalancev2010 INNER JOIN
                        GL00100 ON GL00100.ACTNUMBR_1 = dbo.vw_GLTrialBalancev2010.SEGMENT1 AND GL00100.ACTNUMBR_2 = dbo.vw_GLTrialBalancev2010.SEGMENT2

That displays the below results:-

enter image description here

Can anyone show me a way to group and then subtotal by Category.

I would like to see something like the below:-

enter image description here

All help greatly appreciated.

Upvotes: 0

Views: 45

Answers (1)

juergen d
juergen d

Reputation: 204756

SELECT 
  FISCALYEAR, 
  DATENAME(Month,JVDate) as Month,
  ACCOUNTNUMBER,
  ACCOUNTDESCRIPTION, 
  CATEGORY, 
  POSTINGTYPE,
  sum(ORIGNETAMOUNT) as ORIGNETAMOUNT, 
FROM dbo.vw_GLTrialBalancev2010 
INNER JOIN GL00100 
        ON GL00100.ACTNUMBR_1 = dbo.vw_GLTrialBalancev2010.SEGMENT1 
        AND GL00100.ACTNUMBR_2 = dbo.vw_GLTrialBalancev2010.SEGMENT2
group by FISCALYEAR, DATENAME(Month,JVDate) as Month, ACCOUNTNUMBER, 
         ACCOUNTDESCRIPTION, CATEGORY, POSTINGTYPE

Upvotes: 1

Related Questions