Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Need help with multiple SUMs AND UNION on the same table

    Hello All,
    I am in need of some assistance with a query.

    Based on the query below, I would like to show YTDSales, PYTDSales, YTDProfit, and PYTDProfit for every customer.

    I assume that I need a subquery, but my attempts have failed. Through much trial and error, this is as far as I have got:

    SELECT
    DISTINCT S.CustomerName,
    SUM(S.TotSales) AS YTDSales,
    SUM(S.PubProfit) AS YTDPubProfit
    FROM vInvoiceSummary S INNER JOIN ARCusts ON S.CustAcct=ARCusts.CustAcct INNER JOIN ARSmen S1 ON ARCusts.SmanID=S1.SmanID
    WHERE S.InvoiceDate BETWEEN '1/1/' + cast(DATEPART(YYYY,GETDATE()) as varchar(4)) AND GETDATE() AND S1.SalesmanName = 'HOUSE'
    GROUP BY S.CustomerName
    UNION
    SELECT
    DISTINCT S.CustomerName,
    SUM(S.TotSales) AS PYTDSales,
    SUM(S.PubProfit) AS PYTDPubProfit
    FROM vInvoiceSummary S INNER JOIN ARCusts ON S.CustAcct=ARCusts.CustAcct INNER JOIN ARSmen S1 ON ARCusts.SmanID=S1.SmanID
    WHERE S.InvoiceDate BETWEEN '1/1/' + cast(DATEPART(YYYY,DATEADD(YEAR,-1,GETDATE())) as varchar(4)) AND DATEADD(YEAR,-1,GETDATE()) AND S1.SalesmanName = 'HOUSE'
    GROUP BY S.CustomerName

    This results in the following:
    CustomerName______YTDSales_____YTDProfit
    ---------------------------------------------
    Customer A..............2066.57..........619.28
    Customer A..............3482.54..........1061.14
    Customer B..............369.46............62.41
    Customer C..............0...................-116.43
    Customer D..............244.71............107.17
    Customer D..............617.15.............41.1

    I'm trying to get this:
    CustomerName_____YTDSales____PYTDSales____YTDProfi t____PYTDProfit
    -----------------------------------------------------------------------
    Customer A............2066.57........3482.54..........619.2 8..........1061.14
    Customer B............369.46..........62.41.............0.. ...............0
    Customer C............0.................-116.43..........0.................0
    Customer D............244.71..........617.15...........107. 17..........41.1

    (I also need to calc the %change YTD vs PYTD on both sales and proift).


    Any help would be greatly appreciated.

    Thanks,
    Todd

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT S.CustomerName
         , SUM(CASE WHEN EXTRACT(YEAR FROM S.InvoiceDate) 
                       = EXTRACT(YEAR FROM CURRENT_DATE) THEN S.TotSales END) AS YTDSales
         , SUM(CASE WHEN EXTRACT(YEAR FROM S.InvoiceDate) 
                       = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN S.TotSales END) AS PYTDSales
         , SUM(CASE WHEN EXTRACT(YEAR FROM S.InvoiceDate) 
                       = EXTRACT(YEAR FROM CURRENT_DATE) THEN S.PubProfit END) AS YTDPubProfit
         , SUM(CASE WHEN EXTRACT(YEAR FROM S.InvoiceDate) 
                       = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN S.PubProfit END) AS PYTDPubProfit
      FROM vInvoiceSummary S 
    INNER 
      JOIN ARCusts 
        ON ARCusts.CustAcct = S.CustAcct=
    INNER 
      JOIN ARSmen S1 
        ON S1.SmanID = ARCusts.SmanID
       AND S1.SalesmanName = 'HOUSE'
     WHERE EXTRACT(YEAR FROM S.InvoiceDate) 
        >= EXTRACT(YEAR FROM CURRENT_DATE) - 1
    GROUP 
        BY S.CustomerName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •