Oracle - Group by Cube - Sum by distinct column

Question

Sample data:

product_type |segment_type  |promotion_id  |promotion_value
-----------------------------------------------------------
Beer         |Regional      |1             |20
Beer         |National      |1             |20
Beer         |Regional      |2             |20
Beer         |National      |2             |20
Beer         |National      |3             |30
Beer         |Regional      |4             |40
Soda         |Regional      |5             |50
Soda         |National      |5             |50
Soda         |Regional      |6             |50
Soda         |National      |6             |50
Soda         |National      |7             |15
Soda         |Regional      |8             |20

Objective: Get the total promotion value grouped by product_type and segment_type (cube) considering distinct promotions. Note that a single promotion can reach one or both segments (Regional and National).

Desired result:

product_type   |segment_type    |promotion_value
-------------------------------------------------
Beer           |                |110
Beer           |Regional        |80
Beer           |National        |70
Soda           |                |135
Soda           |Regional        |120
Soda           |National        |115

My current SQL is as follows:

SELECT product_Type,
       segment_type,
       sum(promotion_value)promotion_value
  from sample_data
 group by product_type,
          cube(segment_type)

The current result is:

product_type   |segment_type    |promotion_value
-------------------------------------------------
Beer           |                |150
Beer           |Regional        |80
Beer           |National        |70
Soda           |                |235
Soda           |Regional        |120
Soda           |National        |115

SQLFiddle: link

Is there a way to achieve the desired result?

there is any specific reason why Regional and National shouldn't be added up? — mucio, Feb 13 '15 at 12:56
@mucio: A single promotion can reach both Regional and National segments, the objective is to get the promotion value by distinct promotions. — Mateus Schneiders, Feb 13 '15 at 13:07

Pரதீப் · Answer 1 · 2015-02-13T13:52:55.487

0

Use Sum(Distinct..) to get the sum of unique values in a group

SELECT product_Type,
       segment_type,
       sum(distinct promotion_value)promotion_value
  from sample_data
 group by product_type,
          cube(segment_type)
          order by product_type

SQLFIDDLE DEMO

Update :

SELECT product_Type,
       segment_type,
       Sum(DISTINCT promotion_value)
FROM   (SELECT product_Type,
               segment_type,
               Sum(promotion_value) promotion_value
        FROM   sample_data
        GROUP  BY product_type,
                  segment_type) a
GROUP  BY product_type,
          cube( segment_type )

SQLFIDDLE DEMO

edited Feb 13 '15 at 13:52

answered Feb 13 '15 at 12:52

Pரதீப்

91,748
19
131
172

How would this work with different promotions with the same value? – Mateus Schneiders Feb 13 '15 at 13:04
@Mt.Schneiders - Add `promotion_id` in group by – Pரதீப் Feb 13 '15 at 13:11
Adding promotion_id in the group by will make it repeat the totals per promotion: http://sqlfiddle.com/#!4/f47b3/1 – Mateus Schneiders Feb 13 '15 at 13:21
@Mt.Schneiders - what is the expected output in that case – Pரதீப் Feb 13 '15 at 13:24
Your solution works if all promotions apply for both 'National' and 'Regional' segments, in my case, that's not true 100% of the times. I'll update the question with this new context. – Mateus Schneiders Feb 13 '15 at 15:06
Updated the question. Additionally, it would be nice to set the SQL Fiddles to Oracle. Thanks in advance. – Mateus Schneiders Feb 13 '15 at 15:11

mucio · Answer 2 · 2015-02-13T15:35:17.923

0

EDIT:

I really liked your idea to use cube, never used it before, and I think it's pretty cool if you are dealing with a proper dimensional structured table.

Unfortunately this is not your case. Cube will try to generate the possible total and subtotals, but it's not that smart to understand that it shouldn't add some of your data.

It seems that segment_type and promotion_id are in many-to-many relationship, this is usually not a problem, but cannot be handle automagically by the cube extension.

So at the end I think the best solution, to be on the safe side, is to create two queries to aggregate correctly your data:

   select product_Type, 
          segment_type,
          sum(promotion_value) promotion_value
     from sample_data
 group by product_type,
          segment_type
union all
   select product_Type, 
          null,
          sum(promotion_value) promotion_value
     from (
            select distinct product_Type,
                   promotion_id,
                   promotion_value
              from sample_data
          )
 group by product_type
 order by product_type

Demo in SQLFiddle.

edited Feb 13 '15 at 15:35

answered Feb 13 '15 at 13:24

mucio

7,014
1
21
33

I updated my question to better explain the desired result. Can your check you answer? – Mateus Schneiders Feb 13 '15 at 13:36
I don't think it worked with the new sample data: http://sqlfiddle.com/#!4/d5663/2 – Mateus Schneiders Feb 13 '15 at 13:44
I did few other tests and I don't think this is going to work with `cube`. Check my updated answer – mucio Feb 13 '15 at 15:35
Thanks for the answer. About the structure of the data, my sample data does not come from a single table, it is actually a view from several tables. Additionally, this is a simplified case as I have several more dimensions in reality. If I were to use your solution, i would have one union per dimension, is that correct? – Mateus Schneiders Feb 13 '15 at 15:48
I would say for each level of aggregation – mucio Feb 13 '15 at 15:54

score 0 · Answer 3 · answered Dec 22 '19 at 04:47

0

select product_type,'' as segment_type,sum(promotion_value) as promotion_value from stack_sam group by product_type union select product_type,segment_type,sum(promotion_value) from stack_sam group by product_type,segment_type order by 1 ;

answered Dec 22 '19 at 04:47

praveen

75
1
2

Oracle - Group by Cube - Sum by distinct column

3 Answers3