• Questions
• # how to sum pivot query results

Thanks for the question, Sunil Kumar.

Asked: March 13, 2020 - 11:18 pm UTC

Answered by: Chris Saxon - Last updated: March 19, 2020 - 4:01 pm UTC

Category: SQL - Version: 12c

Viewed 1000+ times

```with t1 as (select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, null period4, 50 period5 from dual
union all
select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, null period5 from dual
union all
select 'dfu1' dfu, 6 type, '01-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
union all
select 'dfu2' dfu, 1 type, '01-Jan-2020' startdt, 40 period1, 50 period2, null period3, null period4, 50 period5 from dual
union all
select 'dfu2' dfu, 4 type, '02-Jan-2020' startdt, 1 period1, 2 period2, null period3, 4 period4, 5 period5 from dual
union all
select 'dfu2' dfu, 6 type, '03-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual)
select * from t1```

```DFU TYPE STARTDT PERIOD1 PERIOD2 PERIOD3 PERIOD4 PERIOD5
dfu1 1 01-Jan-2020 10 20 30 - 50
dfu1 4 01-Jan-2020 1 2 3 4 -
dfu1 6 01-Jan-2020 1 1 1 1 1
dfu2 1 01-Jan-2020 40 50 - - 50
dfu2 4 02-Jan-2020 1 2 - 4 5
dfu2 6 03-Jan-2020 1 1 1 1 1
```

If I take a dfu1 -> it has three records type1, type4, type6

for reach type record -> quantity for 4 periods, period1, period2, period3, period4

based on startdt column, for dfu1:type1:period1 indicates quantity for 01-Jan-2020,
for dfu1:type1:period2 indicates quantity for 01-Jan-2020 + 1 -> which is 02-Jan-2020,
for dfu1:type1:period3 indicates quantity for 01-Jan-2020 + 2 -> which is 03-Jan-2020,
for dfu1:type1:period4 indicates quantity for 01-Jan-2020 + 3 -> which is 04-Jan-2020

and so on... for dfu1:type4, dfu1:type6

now for a dfu1 -> I want to get a sum(qty) group by type,

This can be achieved easily as startdate for a dfu1 is same for all three types.

Whereas as for dfu2

type1, type4 and type6 have three different start dates.

so we cannot just sum (period1) to get period1 value.

for dfu2:type1 -> based on startdt column indicates quantity for 01-Jan-2020, for type 4 and type 6 there is no value for 01-Jan-2020, since type 4 and type 6 startdates and are starting from 02-Jan-2020 and 03-Jan-2020
so dfu2 sum of period 1 will be 40 only

for dfu2 -> period2 value is for 02-Jan-2020 and should calculated as SUM(dfu2:TYPE1:PERIOD2, dfu2:TYPE4:PERIOD1) there is no value for dfu2:type6 for 02-Jan-2020

for dfu2 -> period3 value should be SUM(TYPE1:PERIOD3,TYPE2:PERIOD2,TYPE3:PERIOD1)
for dfu2 -> period4 value should be SUM(TYPE1:PERIOD4,TYPE2:PERIOD3,TYPE3:PERIOD2)
for dfu2 -> period5 value should be SUM(TYPE1:PERIOD5,TYPE2:PERIOD4,TYPE3:PERIOD3)

and so on...

```DFU STARTDT PERIOD1 PERIOD2 PERIOD3 PERIOD4 PERIOD5
dfu1 1-Jan-20 12 23 34 5 51
dfu2 1-Jan-20 40 51 3 1 55
```

Considerations,

startdate should min(startdate)
if there's no value for a period, should be considered as zero.

I was able to get the output by unpivot query, but looking if there's a possibility to achieve the output without unpivot as in my case, am unpivoting, doing the sum and pivoting again

with LiveSQL Test Case:

## and we said...

I'm confused about what exactly is going on here. It feels the table mixes up row and column values... Showing us the pivot/unpivot solution you have would help!

Anyway, to get the output you asked for given the data shown, you could:

- Sum up the values by dfu & startdt
- Use lead to look forward to the next values for each period

Which gives the following rather convoluted query:

```with t1 as (
select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, 0 period4, 50 period5 from dual
union all
select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, 0 period5 from dual
union all
select 'dfu1' dfu, 6 type, '01-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
union all
select 'dfu2' dfu, 1 type, '01-Jan-2020' startdt, 40 period1, 50 period2, 0 period3, 0 period4, 50 period5 from dual
union all
select 'dfu2' dfu, 4 type, '02-Jan-2020' startdt, 1 period1, 2 period2, 0 period3, 4 period4, 5 period5 from dual
union all
select 'dfu2' dfu, 6 type, '03-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
), rws as (
select dfu, startdt,
sum ( period1 ) period1,
sum ( period2 ) period2,
sum ( period3 ) period3,
sum ( period4 ) period4,
sum ( period5 ) period5
from   t1
group  by dfu, startdt
), tots as (
select dfu, startdt,
period1 p1,
period1, 1, 0
) over (
partition by dfu
order by startdt
) p2,
period2, 1, 0
) over (
partition by dfu
order by startdt
period1, 2, 0
) over (
partition by dfu
order by startdt
) p3,
period3, 1, 0
) over (
partition by dfu
order by startdt
period2, 2, 0
) over (
partition by dfu
order by startdt
) p4,
period4, 1, 0
) over (
partition by dfu
order by startdt
period3, 2, 0
) over (
partition by dfu
order by startdt
) p5,
min ( startdt ) over (
partition by dfu
) mn_dt
from   rws
)
select * from tots
where  startdt = mn_dt;

DFU     STARTDT        P1    P2    P3    P4    P5    MN_DT
dfu1    01-Jan-2020       12    23    34     5    51 01-Jan-2020
dfu2    01-Jan-2020       40    51     3     1    55 01-Jan-2020  ```

I'm not convinced this solution meets the business requirement (whatever that is) though. And I'm sure it's more complex than the pivot/unpivot method you have.

Give us more background and we may be able to help better.

(3 ratings)

# Reviews

March 17, 2020 - 11:59 am UTC

Reviewer: Sunil Kumar Noothi from Australia

```       select *
FROM (SELECT dfu,
startdt,
min_der_startdt,
sum_qty,
min_der_startdt - startdt + 1     period
FROM (  SELECT dfu,
startdt,
MIN (der_startdt)     min_der_startdt,
SUM (qty)             sum_qty
FROM (  SELECT dfu,
TYPE,
startdt,
SUBSTR (week, 7, 1)
num,
startdt + TO_NUMBER (SUBSTR (week, 7, 1)) - 1
der_startdt,
qty
FROM scpomgr.test_unpivot
UNPIVOT (QTY
FOR WEEK
IN (PERIOD1,
PERIOD2,
PERIOD3,
PERIOD4,
PERIOD5))
ORDER BY dfu, TYPE, der_startdt)
GROUP BY dfu, startdt, der_startdt
ORDER BY dfu, 2))
;

```

Above is the unpivot query I prepared and on the top of it I have to pivot the query again by dfu.

I have also shared example in google docs as I can't paste screen shot.

Followup

March 17, 2020 - 3:48 pm UTC

Does the query I gave give the result you want? If not, why not?

March 18, 2020 - 2:56 am UTC

Reviewer: Sunil Kumar Noothi from Australia

for the given data set, your query works perfectly fine, based on the example I have given, query you having gvien assumes startdates will be in one day incremental pattern between type1 type4 and type 6

when I added below new combination - to existing result set, for dfu3 results aren't as expected.

```  select 'dfu3' dfu, 1 type, '03-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, 40 period4, 50 period5 from dual
union all
select 'dfu3' dfu, 4 type, '06-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, 5 period5 from dual```

Current output for dfu3 is

```dfu         start_dt                p1      p2     p3   p4   p5    min_dt
dfu3 03-Jan-2020 10 21 32 43 54 03-Jan-2020
```

but expected output for dfu3 is -

dfu start_dt p1 p2 p3 p4 p5 min_dt
dfu3 03-Jan-2020 10 20 30 41 52 03-Jan-2020

p1 -> value will be coming from dfu3:type1 :p1(03-Jan-2020)
p2 -> value will be coming from dfu3:type1 :p2(04-Jan-2020)
p3 -> value will be coming from dfu3:type1 :p3(05-Jan-2020)
p4 -> value will be coming from dfu3:type1:p4 + dfu3:type4:p1 (06-Jan-2020)
p5 -> value will be coming from dfu3:type1:p5+dfu3:type4:p2(07-Jan-2020)

basically between type1, type4, type 6 periods that should be sum is whatever dates matching based on startdates.

My Apologies if am still not able to explain clearly.

Followup

March 18, 2020 - 11:29 am UTC

What exactly do the PERIODN columns represent?

It seems like they're really referring to dates after the start date. Is that the case?

If so, it's much better to store these as rows, not columns. At the very least, unpivoting as in your solution is the logical first step.

And... if the unpivot query works, why are you trying to change the query?

March 18, 2020 - 11:40 am UTC

Reviewer: Sunil Kumar Noothi from Australia

Yes PeriodN represents date.

I have data in Pivot format,
unpivot
achieve result
and I need data again back in Pivot format.

So, am just seeing if I can avoid unpivot and achieve required functionality.

Followup

March 19, 2020 - 4:01 pm UTC

So, am just seeing if I can avoid unpivot and achieve required functionality.

You probably can. But it's likely to be something highly convoluted, like the query in my original answer. I'd stick with unpivot/pivot.

Or - even better - see if you can get the unpivot data to start with!

# More to Explore

##### Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.