Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: SUM and COUNT not producing expected results

    Hi,

    I have 4 tables

    Products:
    BaseProdNum, Description

    Stores:
    StoreID, Name

    Sales:
    BaseProdNum, StoreID, Week, Sales

    NotSelling:
    BaseProdNum, StoreID, Week, Reason

    What I want is for each product, for each store, for each week, sum the sales and count any not selling reasons.
    Both the Sales and NotSelling tables may have multiple rows for the same week, product and store due to other fields in the tables that I'm not interested in.

    So what I have so far is ...

    SELECT Products.BaseProdNum,
    COUNT(CASE WHEN Reason = 'Discontinued' THEN Reason END) AS Discontinued,
    COUNT(CASE WHEN Reason = 'other' THEN Reason END) AS [Exceeded Sales],
    COUNT(CASE WHEN Reason = 'In store' THEN Reason END) AS [In store],
    COUNT(CASE WHEN Reason = 'Not available' THEN Reason END) AS [Not available],
    COUNT(CASE WHEN Reason = 'Stock Record Error' THEN Reason END) AS [Stock Record Error],
    COUNT(NotSelling.Reason) AS [Grand Total], NotSelling.Week, Products.Description,
    SUM(Sales.Sales)
    FROM Products
    LEFT JOIN NotSelling ON dbo.Products.BaseProdNum = NotSelling.BaseProdNum
    INNER JOIN Stores ON Stores.StoreId = NotSelling.StoreId
    LEFT JOIN Sales ON Sales.BaseProdNum = NotSelling.BaseProdNum AND Sales.Week = NotSelling.Week AND Sales.StoreID = NotSelling.StoreID
    GROUP BY Products.BaseProdNum, Products.Description, NotSelling.Week


    which nicely counts the various not selling reasons but the sales summation is very wrong.

    I have tried experimenting with CROSS JOIN Stores instead, however that then mucks up both the count columns and the sum columns.

    Am I trying to do something I can't? I'm quite a newbie to SQL having been rather thrown in at the deep end.

    Any ideas how to solve this?

    Thanks
    Rachel

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Products.BaseProdNum
         , Products.Description    
         , Stores.StoreID 
         , w.Week
         , s.total_sales
         , n.Discontinued
         , n.[Exceeded Sales]
         , n.[In store]
         , n.[Not available]
         , n.[Stock Record Error]
         , n.[Grand Total]     
      FROM Products
    CROSS
      JOIN Stores
    CROSS
      JOIN ( SELECT Sales.Week
               FROM Sales
             UNION
             SELECT NotSelling.Week
               FROM NotSelling ) AS w
    LEFT OUTER
      JOIN ( SELECT BaseProdNum
                  , StoreID 
                  , Week
                  , SUM(Sales) as total_sales
               FROM Sales
             GROUP
                 BY BaseProdNum
                  , StoreID 
                  , Week ) AS s
        ON s.BaseProdNum = Products.BaseProdNum
       AND s.StoreID = Stores.StoreID
       AND s.Week = w.Week
    LEFT OUTER
      JOIN ( SELECT BaseProdNum
                  , StoreID  
                  , Week
                  , COUNT(CASE WHEN Reason = 'Discontinued' THEN Reason END) AS Discontinued
                  , COUNT(CASE WHEN Reason = 'other' THEN Reason END) AS [Exceeded Sales]
                  , COUNT(CASE WHEN Reason = 'In store' THEN Reason END) AS [In store]
                  , COUNT(CASE WHEN Reason = 'Not available' THEN Reason END) AS [Not available]
                  , COUNT(CASE WHEN Reason = 'Stock Record Error' THEN Reason END) AS [Stock Record Error]
                  , COUNT(Reason) AS [Grand Total]
               FROM NotSelling
             GROUP
                 BY BaseProdNum
                  , StoreID  
                  , Week ) AS n
        ON n.BaseProdNum = Products.BaseProdNum
       AND n.StoreID = Stores.StoreID
       AND n.Week = w.Week
    by the way, [column] is not ANSI SQL, it should be "column"

    presumably this is SQL Server or Access?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    Hi,

    Yes its MS SQL, sorry I didn't realise it wasn't standard. Still learning.

    Many thanks for your response.

    Rachel

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

Posting Permissions

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