Trying to Sum by different levels

  • Hello everyone,

    If any one could please help me with this?

    I'm trying to add additional sum by Family and Sum by Store level

    Trying to get this result

    It seem that i only know how to use group by at the end of my statement.

  • you might have to use case statement

    for example if I wanted to get a count for all employees who first name starts with 'j'

    select sum cnt from

    (select  case when firstname like 'j%' then 0 else 1 end  as cnt group by x)

    ok - not the perfect example, but you didn't post your code, so I can only help a little

    but a case statement that converts a string to a 1 or 0 and then you can sum those... it gives you a count function

     

     

     

    MVDBA

  • Are you truly still using SQL 7 or SQL 2000?

  • I am assuming the solution is a correlated subquery.

    Like this example, with the difference that you need to use sum and group by instead of AVG.

    This must be done for each column since its grouped by different conditions.

    Someone correct me if am wrong.

    https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/

    Correlated-Subquery-Illustration

    I want to be the very best
    Like no one ever was

  • Assuming you're using SQL Server version 2005 or later, this should work for you

    SELECT Store,Article,Family,[Article Level Qty],
    SUM([Article Level Qty]) OVER(PARTITION BY Family) AS [Family Level Qty],
    SUM([Article Level Qty]) OVER(PARTITION BY Store) AS [Store Level Qty]
    FROM MyTable

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • ktflash wrote:

    I am assuming the solution is a correlated subquery.

    Like this example, with the difference that you need to use sum and group by instead of AVG.

    This must be done for each column since its grouped by different conditions.

    Someone correct me if am wrong.

    https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/

    Correlated-Subquery-Illustration

    that would be evil in anything before 2016 , thankfully they fixed it

    MVDBA

  • MVDBA (Mike Vessey) wrote:

    ktflash wrote:

    I am assuming the solution is a correlated subquery.

    Like this example, with the difference that you need to use sum and group by instead of AVG.

    This must be done for each column since its grouped by different conditions.

    Someone correct me if am wrong.

    https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/

    Correlated-Subquery-Illustration

    that would be evil in anything before 2016 , thankfully they fixed it

    i assume ur referencing Marks solution as not having to do this?

     

    I want to be the very best
    Like no one ever was

  • ktflash wrote:

    MVDBA (Mike Vessey) wrote:

    ktflash wrote:

    I am assuming the solution is a correlated subquery.

    Like this example, with the difference that you need to use sum and group by instead of AVG.

    This must be done for each column since its grouped by different conditions.

    Someone correct me if am wrong.

    https://www.essentialsql.com/get-ready-to-learn-sql-server-20-using-subqueries-in-the-select-statement/

    Correlated-Subquery-Illustration

    that would be evil in anything before 2016 , thankfully they fixed it

    i assume ur referencing Marks solution as not having to do this?

    not at all - way back in sql 2000 (the forum we are in) inline sub selects acted a little bit like a function, things that were beaten out of us with a heavy stick (like cursors and other dangerous activities)

    MS have optimised it a bit, but I like mark's solution  - it's small and neat (i'd love to see a comparison of the subselect  vs partion/order by)

     

    MV

    MVDBA

  • Thank you Mark and everyone who replied, many thanks for all the help.

    I am now using this code

    select

    STRSTORECODE as STORE,

    IT.STRXREFCODE as ARTICLE,

    IT.STRFAMILYCODE as FAMILY,

    DBLSTOCKONHAND as [Article Level Qty],

    SUM([DBLSTOCKONHAND]) OVER(PARTITION BY IT.STRFAMILYCODE) AS [Family Level Qty],

    SUM([DBLSTOCKONHAND]) OVER(PARTITION BY STRSTORECODE) AS [Store Level Qty]

    from STORERNG SR

    join ITEM AS IT on IT.LINTITEMNUMBER=SR.LINTITEMNUMBER

    Where STRSTORECODE in ('SL07','SL10')

    and DBLSTOCKONHAND <>'0'

    and STRFAMILYCODE='011-29656'

    order by STRSTORECODE,IT.STRXREFCODE,IT.STRFAMILYCODE

    Which returns me

    Because it's combining the family stocks for 2 stores.

    I would like to Separate out or group the Family by store.

    How am i able to achieve it?

  • Hi Everyone!

    I did a search and amend my code to

    SUM([DBLSTOCKONHAND]) OVER(PARTITION BY STRSTORECODE,IT.STRFAMILYCODE) AS [Family Level Qty],

    It's working now, thank you everyone and have a great day! Cheers to such a helpful community.

Viewing 10 posts - 1 through 10 (of 10 total)

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