Post reply

Subtotal in Group Statement

  • February 16, 2010 at 3:11 am

    #230050

    Hi

    I am looking to add a subtotal by Account, Family Name and by Year. I have this code below but do not know how I can add the subtotals I need. I have experimented with ROLLUP but this has proved unsuccessful. I would really appreciate any help.:-)

    I woule like to see results like :

    Account FAMILY_NAME YEAR1 YEAR2

    TEST123 TESTFAMILY £45 £145

    TEST123 TESTFAMILY2 etc....

    SELECT dbo.ACCOUNT,dbo.QTY *dbo.PRICE AS [Qty x Price], dbo.DATE, dbo.FAMILY_NAME

    FROM dbo.SalesHistory

    WHERE (dbo.DATE >= DATEADD(YEAR, - 2, GETDATE()))

    GROUP BY dbo.ACCOUNT,FAMILY_NAME,DATE,QTY,PRICE

  • February 16, 2010 at 4:53 am

    #1119169

    Can you provide table structure and sample data?

  • February 16, 2010 at 5:46 am

    #1119203

    Hi

    Attached are the tables that I have used in this View and some sample data.

    This is being picked up by BIDS and then displayed in SSRS. My end goal with this query is to try and show by account the sales grouped by product family for year 1 and year 2 on a rolling basis.

    Below is the full query I have so far:

    SELECT dbo.SalesHistory.ACCOUNT, dbo.SalesHistory.PART, dbo.Stock.DESCRIPTION, dbo.Stock.FAMILY, dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE AS [Qty x Price],

    dbo.SalesHistory.DISC1, dbo.CUSTOMER.POST, dbo.SalesHistory.DATE, dbo.Family.FAMILY_NAME

    FROM dbo.SalesHistory INNER JOIN

    dbo.Stock ON dbo.SalesHistory.PART = dbo.Stock.PART INNER JOIN

    dbo.Family ON dbo.Stock.FAMILY = dbo.Family.FAMILY LEFT OUTER JOIN

    dbo.CUSTOMER ON dbo.SalesHistory.ACCOUNT = dbo.CUSTOMER.ACCOUNT

    WHERE (dbo.Stock.FAMILY IN ('800', '600', '1400', '2200', '2000', '5600', '200', '3000', '3600', '5200')) AND (dbo.SalesHistory.DATE >= DATEADD(YEAR, - 2, GETDATE()))

  • February 16, 2010 at 6:53 am

    #1119243

    If you want to show sales by account and product family and year on columns, you can create a matrix report in SSRS using a query like:

    SELECT dbo.SalesHistory.ACCOUNT, dbo.Stock.FAMILY, YEAR(dbo.SalesHistory.DATE)

    SUM(dbo.SalesHistory.QTY * dbo.SalesHistory.PRICE) AS [Qty x Price]

    FROM dbo.SalesHistory INNER JOIN dbo.Stock

    ON dbo.SalesHistory.PART = dbo.Stock.PART

    GROUP BY dbo.SalesHistory.ACCOUNT, dbo.Stock.FAMILY, YEAR(dbo.SalesHistory.DATE)

  • February 16, 2010 at 7:16 am

    #1119267

    Brilliant, thank you very much for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply