none
GROUP BY with HAVING SUM not returning what I'd expect RRS feed

  • Question

  • Hi Folks -


    I have the following data in my table table:


    I'm trying to write a query so that I pull in all relevant records for a given LOCATION_ID that have TOTAL SALES_RETAIL <> 0 across ALL Departments.

    Here is my query:

    SELECT 
        ORG.LOCATION_ID,
        SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        SAL.SALES_RETAIL,
        NVL(BUD.SALES_BUDGET,0) AS SALES_BUDGET
    FROM 
        F_DEPARTMENT SAL 
            LEFT JOIN F_BUDGET BUD ON SAL.MRP_ORGANIZATION_KEY = BUD.MRP_ORGANIZATION_KEY
                AND SAL.MRP_CALENDAR_KEY = BUD.MRP_CALENDAR_KEY AND SAL.MRP_DEPARTMENT_KEY = BUD.MRP_DEPARTMENT_KEY
            INNER JOIN D_ORGANIZATION ORG ON SAL.MRP_ORGANIZATION_KEY = ORG.MRP_ORGANIZATION_KEY     
    WHERE SAL.MRP_ORGANIZATION_KEY = 601 AND SAL.MRP_CALENDAR_KEY = 20191123            
    GROUP BY 
        ORG.LOCATION_ID,SAL.MRP_ORGANIZATION_KEY, SAL.MRP_CALENDAR_KEY, SAL.MRP_DEPARTMENT_KEY,BUD.MRP_ORGANIZATION_KEY,BUD.MRP_CALENDAR_KEY,
        BUD.MRP_DEPARTMENT_KEY,
        SAL.SALES_RETAIL,
        BUD.SALES_BUDGET
    HAVING SUM(SAL.SALES_RETAIL) <> 0

    This query excludes the row I have highlighted in RED above when in reality, I need to include that.  Am I doing anything wrong?

    Thank you!

    Tuesday, January 14, 2020 2:24 PM

All replies

  • Your statement says sl.sales_retail has to be not equal to zero. That record is zero so it will not be included.
    Tuesday, January 14, 2020 2:43 PM
  • Correct, but I'm trying to modify it where the TOTAL sales across all Department for a given LOCATION_ID is <>0, not a specific Department...  ANy ideas?
    Tuesday, January 14, 2020 2:47 PM
  • You need to put a group by statement in your code is you want a total.
    Tuesday, January 14, 2020 3:38 PM
  • I already have a GROUP BY statement in my code, see above. Do I have it placed incorrectly?
    Tuesday, January 14, 2020 4:05 PM
  • You do not have any aggregates in your select statement so you will return every row. 

    "I'm trying to write a query so that I pull in all relevant records for a given LOCATION_ID that have TOTAL SALES_RETAIL <> 0 across ALL Departments. "

    Don't you need a sum on your select statement?

    More like this?

    SELECT 
        ORG.LOCATION_ID,
        --SAL.MRP_ORGANIZATION_KEY,
        --SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        sum(SAL.SALES_RETAIL) as 'SALES_RETAIL',
        sum(NVL(BUD.SALES_BUDGET,0)) AS SALES_BUDGET
    FROM 
        F_DEPARTMENT SAL 
            LEFT JOIN F_BUDGET BUD ON SAL.MRP_ORGANIZATION_KEY = BUD.MRP_ORGANIZATION_KEY
                AND SAL.MRP_CALENDAR_KEY = BUD.MRP_CALENDAR_KEY AND SAL.MRP_DEPARTMENT_KEY = BUD.MRP_DEPARTMENT_KEY
            INNER JOIN D_ORGANIZATION ORG ON SAL.MRP_ORGANIZATION_KEY = ORG.MRP_ORGANIZATION_KEY     
    WHERE SAL.MRP_ORGANIZATION_KEY = 601 AND SAL.MRP_CALENDAR_KEY = 20191123            
    GROUP BY 
        ORG.LOCATION_ID
    	--,SAL.MRP_ORGANIZATION_KEY, SAL.MRP_CALENDAR_KEY, SAL.MRP_DEPARTMENT_KEY,BUD.MRP_ORGANIZATION_KEY,BUD.MRP_CALENDAR_KEY,
        BUD.MRP_DEPARTMENT_KEY,
        --SAL.SALES_RETAIL,
        --BUD.SALES_BUDGET
    HAVING SUM(SAL.SALES_RETAIL) <> 0

    Tuesday, January 14, 2020 4:10 PM
  • Thank you for the help!  I tried this (same as yours) and no luck as it still doesn't include the Department 3 above where SALES_RETAIL is 0(in my screen shot):

    SELECT 
        ORG.LOCATION_ID,
        --SAL.MRP_ORGANIZATION_KEY,
        --SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        SUM(SAL.SALES_RETAIL) AS SALES_RETAIL,
        SUM(NVL(BUD.SALES_BUDGET,0)) AS SALES_BUDGET
    FROM 
        F_DEPARTMENT SAL 
            LEFT JOIN F_BUDGET BUD ON SAL.MRP_ORGANIZATION_KEY = BUD.MRP_ORGANIZATION_KEY
                AND SAL.MRP_CALENDAR_KEY = BUD.MRP_CALENDAR_KEY AND SAL.MRP_DEPARTMENT_KEY = BUD.MRP_DEPARTMENT_KEY
            INNER JOIN D_ORGANIZATION ORG ON SAL.MRP_ORGANIZATION_KEY = ORG.MRP_ORGANIZATION_KEY     
    WHERE SAL.MRP_ORGANIZATION_KEY = 601 AND SAL.MRP_CALENDAR_KEY = 20191123            
    GROUP BY 
        ORG.LOCATION_ID,
        --SAL.MRP_ORGANIZATION_KEY, 
        --SAL.MRP_CALENDAR_KEY, 
        SAL.MRP_DEPARTMENT_KEY
        --BUD.MRP_ORGANIZATION_KEY,
        --BUD.MRP_CALENDAR_KEY,
        --BUD.MRP_DEPARTMENT_KEY,
        --SAL.SALES_RETAIL,
        --BUD.SALES_BUDGET
    HAVING SUM(SAL.SALES_RETAIL) <> 0


    • Edited by cdtakacs1 Tuesday, January 14, 2020 4:29 PM
    Tuesday, January 14, 2020 4:28 PM
  • Why would you expect it to? Sales_Retail is 0 so the sum would be 0 and you are excluding anything = 0. Maybe you need to have an "OR" as part of your having statement.

    OR sum(bud.sales_budget) <>0

    Tuesday, January 14, 2020 4:38 PM
  • Yes, SALES_RETAIL for that Department is 0 but for the LOCATION_ID it is not...I need to include ALL DEPARTMENTS if their TOTAL SALES_RETAIL <> 0 for a given LOCATION_ID.  Does that make sense?
    Tuesday, January 14, 2020 4:46 PM
  • Then comment out the MRP_department_key. It sounds like you need to understand what the group by statement does and how it works.
    Tuesday, January 14, 2020 4:52 PM
  • This worked, thank you!

    The only thing is, I need to include the Department ID in my query as I use it to elsewhere.  Is there anyway to do that?

    • Edited by cdtakacs1 Tuesday, January 14, 2020 5:05 PM
    Tuesday, January 14, 2020 5:00 PM
  • Is something like this what you want?

    Declare @Sample Table(Department int, Sales decimal(10,2));
    Insert @Sample(Department, Sales) Values
    (1, 25),
    (1, 0),
    (1, 37),
    (2, 0),
    (3, 14);
    
    ;With cte As
    (Select Department, Sales, Sum(Sales) Over(Partition By Department) As TotalSales
    From @Sample)
    Select Department, Sales
    From cte
    Where TotalSales <> 0;
    
    /* Result
    Department  Sales
    ----------- -------
    1           25.00
    1           0.00
    1           37.00
    3           14.00
    
    */

    Tom

    Tuesday, January 14, 2020 5:02 PM
  • Hi Tom -i

    Yes that's exactly it! I do however still need to display the Department, Organization and Calendar in my query results...but with GROUP BY it seems i can't?

    Tuesday, January 14, 2020 5:44 PM
  • I didn't exactly understand what you were looking for, but maybe this can get you started:

    SELECT     ORG.LOCATION_ID,
        SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        SAL.SALES_RETAIL,
        isnull(BUD.SALES_BUDGET,0) AS SALES_BUDGET
    FROM     F_DEPARTMENT SAL         LEFT JOIN (SELECT MRP_ORGANIZATION_KEY,  MRP_CALENDAR_KEY, MRP_DEPARTMENT_KEY, SUM(SALES_BUDGET) AS SALES_BUDGET
                       FROM   F_BUDGET
                       GROUP  BY MRP_ORGANIZATION_KEY,  MRP_CALENDAR_KEY, MRP_DEPARTMENT_KEY) AS BUD
                  ON SAL.MRP_ORGANIZATION_KEY = BUD.MRP_ORGANIZATION_KEY
                AND SAL.MRP_CALENDAR_KEY = BUD.MRP_CALENDAR_KEY             AND SAL.MRP_DEPARTMENT_KEY = BUD.MRP_DEPARTMENT_KEY
            INNER JOIN D_ORGANIZATION ORG ON SAL.MRP_ORGANIZATION_KEY = ORG.MRP_ORGANIZATION_KEY     WHERE SAL.MRP_ORGANIZATION_KEY = 601   AND SAL.MRP_CALENDAR_KEY = 20191123           


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 14, 2020 11:05 PM
  • Thank you so much! It says missing keyword line 9. BUD is underlined.
    Tuesday, January 14, 2020 11:12 PM
  • If I understand then you don't need (or want) a GROUP BY, just the windowed function in a CTE and your query should be

    ;WITH cte AS
    (SELECT 
        ORG.LOCATION_ID,
        SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        SAL.SALES_RETAIL,
        NVL(BUD.SALES_BUDGET,0) AS SALES_BUDGET,
        SUM(SAL.SALES_RETAIL) OVER(PARTITION BY ORG.LOCATION_ID) AS TOTAL_LOCATION_SALES
    FROM 
        F_DEPARTMENT SAL 
            LEFT JOIN F_BUDGET BUD ON SAL.MRP_ORGANIZATION_KEY = BUD.MRP_ORGANIZATION_KEY
                AND SAL.MRP_CALENDAR_KEY = BUD.MRP_CALENDAR_KEY AND SAL.MRP_DEPARTMENT_KEY = BUD.MRP_DEPARTMENT_KEY
            INNER JOIN D_ORGANIZATION ORG ON SAL.MRP_ORGANIZATION_KEY = ORG.MRP_ORGANIZATION_KEY     
    WHERE SAL.MRP_ORGANIZATION_KEY = 601 AND SAL.MRP_CALENDAR_KEY = 20191123)
    
    SELECT 
        cte.LOCATION_ID,
        cte.MRP_ORGANIZATION_KEY,
        cte.MRP_CALENDAR_KEY,
        cte.MRP_DEPARTMENT_KEY,
        cte.SALES_RETAIL,
        cte.SALES_BUDGET
    FROM cte
    WHERE TOTAL_LOCATION_SALES <> 0

    Tom

    • Proposed as answer by Lily Lii Thursday, January 16, 2020 2:57 AM
    Tuesday, January 14, 2020 11:15 PM
  • Hi cdtakacs1,

    Very glad you have got the solution to your confusion. If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 15, 2020 2:40 AM
  • Hi Tom -

    Wow, that's great!!!

    So just to clarify, if there are NO Sales across any of the departments for a given LOCATION_ID, it WONT be included in the results, right?

    Wednesday, January 15, 2020 3:45 PM
  • Thank you so much! It says missing keyword line 9. BUD is underlined.

    We have been talking about this before, haven't we? :-) This is an SQL Server forum, and I changed your NVL to isnull to get something that actually can run on SQL Server. If you absolutely want something that runs on Oracle, you better ask in an Oracle forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 15, 2020 10:49 PM
  • I believe so, yes.  But since you didn't give us tables and data, we can't really test it.  So the way to find out is for you to try it and see if it works.

    If you wanted something that we have tested, you should supply

    1) Queries to CREATE your table(s)
    2) Queries  to INSERT sample data.
    3) The desired result given the sample data.
    4) A short description of the business rules.
    5) Which version of SQL Server you are using.

    And, if you want an Oracle solution, you should do that on an Oracle forum. 

    Tom

    Thursday, January 16, 2020 3:33 AM
  • (SAL.SALES_RETAIL) <> 0

    this is why

    the sales retail value for this is 0

    Thursday, January 16, 2020 4:18 AM
  • Why did you post a pretty colored picture, instead of the DDL for your table? I hope you realize there's no such thing as an "<attribute>_key" in a valid relational model. Declaring something to be a key is how it's used, not what it is by its nature. And we have no idea what the key for your table is. Now we want to help you. We have to look at your pretty picture and transcribe everything to tables, guessing at the datatypes, constraints and everything else. But you're not paying us! ? You also don't seem to know the difference between a row and a record, because you're still thinking in terms of spreadsheets and not a relational database. 

    I also see you used the NVL, which is part of Oracle and has nothing to do with SQL Server. The all uppercase makes it look like you're still using punch cards and that you don't care anything about somebody being able to read your code. 

    Want to try again and follow Netiquette? Or post on an Oracle forum? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, January 16, 2020 10:02 PM
  • Tom -

    Thank you so much! This was a HUGE help and solves my issue. I can't thank you enough, much appreciated.

    Have a great weekend!

    Thursday, January 16, 2020 11:44 PM
  • Hi cdtakacs1,

    Thank you very much for your reply. It's so kind of you to mark Tom's reply as answer in order to close this thread. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 17, 2020 7:54 AM