15,610,088 members
1.00/5 (1 vote)
See more:
I am working on a partitioning problem. I am looking to partition an entire table using three different ranges:
ColumnA > 0.0
ColumnA <= 0.0
ColumnB -20.0 <= AND <=20.0

Then, I need to apply mathematical expressions to each partition:
Mean(ColumnA)
Max(ColumnA)
Min(ColumnB)

The third part of the problem is to create a table populating Columns using the output from the 3 partitions:

Median
Max
Min

What I have tried:

```This is the solution I came up with:

CREATE TABLE Table2 AS
(SELECT

MEAN(ColumnA) OVER p0,
MAX (ColumnA) OVER p0,
MIN(ColumnA) OVER p0,
MEAN(ColumnA) OVER p1,
MAX (ColumnA) OVER p1,
MIN(ColumnA OVER p1,
MEAN(ColumnB) OVER p2,
MAX (ColumnB) OVER p2,
MIN(ColumnB) OVER p2,
FROM Table1,
WINDOW p0 AS (PARTITION BY WHERE ColumnA > 0.0) AS MEAN,
WINDOW p1 AS (PARTITION BY WHERE ColumnA <=0.0) AS MAX,
WINDOW p2 AS (PARTITION BY WHERE ColumnB >=-20.0 AND <=20.0) Min;

I want to make sure this is correct. I am concerned I am confused as to how I am applying the mathematical functions to each partition and populating the new table with the data. The wording of the problem seems unclear to me. I am also wondering if there is a better way to write this?  Maybe using a Partition By Range clause? Or, a Cross Apply? Open to suggestions. ```
Posted
Updated 8-Nov-21 7:45am
v2
CHill60 9-Nov-21 9:18am
It would be so much easier to help you if you had shared some sample data and expected results.
Also your "ranges" are not clear ... if column B is between -20 and +20 is that a "range" regardless of the value in column B?

EDIT: And what database is this, I'm not familiar with this syntax
Arvin Donner 11-Nov-21 13:15pm
There is no data because it's a hypothetical problem. I just want to know if the code is valid and see if someone has any better alternatives to how to solve a problem like this than what I came up with.

And, Yes the ColumnB ">= -20.0 AND <=20.0" is a range.