SQL aggregate function with different groupings

Question

I'm struggling with a SQL aggregate function. I need to run SUM function and GROUP BY, but at the same time add total without grouping as a measuring data. Here is a simple demo dataset:

select a.item, sum(a.value) from SALES a
group by a.item, a.region

Result is essentially this:

SALES       
    ITEM    REGION  Sum(Value)
    Apple   North      4
    Orange  South      7
    Apple   South      3
    Apple   East       5
    Orange  East       6
    Orange  North      5

This works fine to summarize item sales and group them based on region and item. What I'm missing though is that I'm not after summarized values per se, but rather the ratios of sales by region.

I.e. Apples sold 4 + 3 + 5 over different regions, so 12 in total and I'm looking for the sales share of each region in total sales. If I could only present sales ratios in the query without actual sales numbers, even better, however not crucial.

I'm currently after how to add another column where all sales of Apples or Oranges would be summarized in a separate column, so I could divide them to get an answer, but I'm not sure how to do that nor am I sure if that's the right way to move forward.

But it would be looking for something like that:

SALES           
    ITEM    REGION  Value   Total
    Apple   North   4        12
    Orange  South   7        18
    Apple   South   3        12
    Apple   East    5        12
    Orange  East    6        18
    Orange  North   5        18

I'm not sure on the db engine, but regular SQL seems to work. I will end up running this query from Excel, so I could utilize Excel tools to divide region sales with total sales to get the shares, however if this could be handled by the query itself, then it would clearly be far superior.

Any help is much appreciated!

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2018-10-03 21:33:39 +00:00
Commented Oct 3, 2018 at 21:33 — Gordon Linoff
– Gordon Linoff, Commented Oct 3, 2018 at 21:33

Gordon Linoff · Accepted Answer · 2018-10-03 21:35:00Z

1

I think you just want a window function:

select s.item, s.region, sum(s.value),
       sum(sum(s.value)) over (partition by item) as region_total
from SALES s
group by s.item, s.region;

answered Oct 3, 2018 at 21:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL aggregate function with different groupings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related