Savvas Savvides - 1 year ago 38

SQL Question

I am learning how to use windowing functions and came across this SQL statement.

`select`

...

sum(ss_ext_sales_price) * 100 / sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio

from

...

where

...

group by

i_item_id,

i_item_desc,

i_category,

i_class,

i_current_price

My question is whether the

`i_class`

`SUM`

`group by`

Answer

Window functions and aggregation functions . . . they look very awkward when you first start using them together. The partitioning clause has nothing to do with the first `sum()`

, so let's just look at the denominator:

```
sum( sum(ss_ext_sales_price) ) over (partition by i_class) as revenueratio
```

The `over`

applies to the first `sum()`

. This is taking calculate the sum of the sum column . . . that is, the total amount. I think it helped me to break this out:

```
sum(ss_ext_sales_price)
sum( sum(ss_ext_sales_price) ) over (partition by i_class) as revenueratio
```

In fact, you are not allowed to have a window function as an argument to an aggregation function:

```
sum( sum(ss_ext_sales_price) over (partition by i_class) ) as revenueratio
```

Source (Stackoverflow)