Trying to Sum by different levels

Question

Post reply

Trying to Sum by different levels

louislsh

Old Hand

Points: 358
More actions
March 2, 2020 at 9:55 am
Go to Answer
#3730072

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.
Mark Cowne

One Orange Chip

Points: 26952
More actions
March 2, 2020 at 12:57 pm
Answer
#3730109
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

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 2

Are you truly still using SQL 7 or SQL 2000?

How to post data/code on a forum to get the best help.

ktflash Default port Points: 1400 More actions · Answer 3

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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

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/

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

MVDBA

ktflash Default port Points: 1400 More actions · Answer 5

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/
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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

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/
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

louislsh Old Hand Points: 358 More actions · Answer 7

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?

louislsh Old Hand Points: 358 More actions · Answer 8

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.