Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

SQL pivot and sum total

Tags:

oracle-database

sql

I am using oracle 19.24. I am debugging my SQL query and found some weird thing. Here are testing scripts:

create table test_payment
(
  payment_date date,
  payment_sum number
);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-03-2015', 'dd-mm-yyyy'), 149);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-10-2019', 'dd-mm-yyyy'), 15);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-10-2019', 'dd-mm-yyyy'), 117);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-11-2024', 'dd-mm-yyyy'), 27);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-09-2020', 'dd-mm-yyyy'), 369);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-07-2020', 'dd-mm-yyyy'), 199);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-08-2023', 'dd-mm-yyyy'), 118);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-04-2016', 'dd-mm-yyyy'), 48);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('20-09-2019', 'dd-mm-yyyy'), 239);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-05-2021', 'dd-mm-yyyy'), 78);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-02-2018', 'dd-mm-yyyy'), 265);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-01-2019', 'dd-mm-yyyy'), 89);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-12-2021', 'dd-mm-yyyy'), 175);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-04-2024', 'dd-mm-yyyy'), 249);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-06-2015', 'dd-mm-yyyy'), 125);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-06-2021', 'dd-mm-yyyy'), 3);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-02-2024', 'dd-mm-yyyy'), 55);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-04-2018', 'dd-mm-yyyy'), 30);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-11-2020', 'dd-mm-yyyy'), 259);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-06-2016', 'dd-mm-yyyy'), 279);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-10-2024', 'dd-mm-yyyy'), 5);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('08-02-2021', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('21-09-2020', 'dd-mm-yyyy'), 58);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-01-2024', 'dd-mm-yyyy'), 38);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-09-2020', 'dd-mm-yyyy'), 76);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-07-2017', 'dd-mm-yyyy'), 44);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-07-2021', 'dd-mm-yyyy'), 14);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-10-2019', 'dd-mm-yyyy'), 159);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('29-03-2023', 'dd-mm-yyyy'), 118);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('19-11-2022', 'dd-mm-yyyy'), 135);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-12-2018', 'dd-mm-yyyy'), 19);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-12-2018', 'dd-mm-yyyy'), 45);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-05-2015', 'dd-mm-yyyy'), 14);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('04-03-2022', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-03-2018', 'dd-mm-yyyy'), 74);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('07-06-2015', 'dd-mm-yyyy'), 129);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('08-08-2019', 'dd-mm-yyyy'), 51);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('20-12-2024', 'dd-mm-yyyy'), 6);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('03-03-2024', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('14-04-2017', 'dd-mm-yyyy'), 5);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('16-07-2016', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-11-2015', 'dd-mm-yyyy'), 115);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-12-2022', 'dd-mm-yyyy'), 135);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-06-2017', 'dd-mm-yyyy'), 45);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('31-08-2023', 'dd-mm-yyyy'), 327);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-06-2020', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('17-10-2023', 'dd-mm-yyyy'), 189);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-12-2019', 'dd-mm-yyyy'), 225);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('22-02-2015', 'dd-mm-yyyy'), 25);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-02-2017', 'dd-mm-yyyy'), 199);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('06-03-2021', 'dd-mm-yyyy'), 5);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-03-2022', 'dd-mm-yyyy'), 79);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-02-2022', 'dd-mm-yyyy'), 219);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-06-2024', 'dd-mm-yyyy'), 30);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-04-2018', 'dd-mm-yyyy'), 369);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-06-2022', 'dd-mm-yyyy'), 209);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-07-2023', 'dd-mm-yyyy'), 499);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-11-2023', 'dd-mm-yyyy'), 130);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('01-10-2020', 'dd-mm-yyyy'), 149);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-12-2022', 'dd-mm-yyyy'), 15);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-11-2017', 'dd-mm-yyyy'), 175);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-09-2018', 'dd-mm-yyyy'), 255);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-08-2020', 'dd-mm-yyyy'), 255);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('21-01-2019', 'dd-mm-yyyy'), 179);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('07-01-2015', 'dd-mm-yyyy'), 105);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-09-2017', 'dd-mm-yyyy'), 48);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-09-2021', 'dd-mm-yyyy'), 115);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-08-2023', 'dd-mm-yyyy'), 659);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('10-06-2019', 'dd-mm-yyyy'), 13);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('10-09-2015', 'dd-mm-yyyy'), 595);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('17-03-2015', 'dd-mm-yyyy'), 68);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('10-04-2017', 'dd-mm-yyyy'), 71);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('06-04-2023', 'dd-mm-yyyy'), 28);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('01-04-2018', 'dd-mm-yyyy'), 179);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-08-2015', 'dd-mm-yyyy'), 159);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('14-11-2023', 'dd-mm-yyyy'), 1);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-06-2020', 'dd-mm-yyyy'), 125);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-04-2023', 'dd-mm-yyyy'), 51);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-10-2017', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('17-03-2022', 'dd-mm-yyyy'), 229);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-08-2021', 'dd-mm-yyyy'), 29);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-06-2017', 'dd-mm-yyyy'), 38);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('08-07-2024', 'dd-mm-yyyy'), 249);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-11-2022', 'dd-mm-yyyy'), 65);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-12-2018', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-10-2018', 'dd-mm-yyyy'), 129);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('20-06-2024', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('29-11-2020', 'dd-mm-yyyy'), 29);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-06-2022', 'dd-mm-yyyy'), 209);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-04-2024', 'dd-mm-yyyy'), 119);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-12-2020', 'dd-mm-yyyy'), 299);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('03-02-2024', 'dd-mm-yyyy'), 238);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-06-2016', 'dd-mm-yyyy'), 89);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-09-2023', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('13-04-2021', 'dd-mm-yyyy'), 20);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-10-2016', 'dd-mm-yyyy'), 59);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-09-2016', 'dd-mm-yyyy'), 15);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-10-2018', 'dd-mm-yyyy'), 149);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('21-10-2016', 'dd-mm-yyyy'), 85);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-06-2019', 'dd-mm-yyyy'), 24);

This query returns 12482, nothing special

select
sum(for_total)
from (
  select
    extract(year from payment_date) "year",
    payment_sum for_total,
    payment_sum
  from test_payment
)

But this query returns 9003!

select
sum(for_total)
from (
  select
    extract(year from payment_date) "year",
    payment_sum for_total,
    payment_sum
  from test_payment
)
pivot
(
  sum(payment_sum) for "year" in (2020,2021,2022,2023,2024)
)

The idea obviously is this:

select
sum("2020"),sum("2021"),sum("2022"),sum("2023"),sum("2024"),
sum(for_total)
from (
  select
    extract(year from payment_date) "year",
    payment_sum for_total,
    payment_sum
  from test_payment
)
pivot
(
  sum(payment_sum) for "year" in (2020,2021,2022,2023,2024)
)

Get sum for last 5 years and grand total (not only for 5 year, total sum) at the end. And by the way, sum for last 5 years are 7000.

Why pivot changes total sum? It seems that I don't know or don't understand something about pivot.

like image 277
Vitaly Avatar asked Jul 29 '26 19:07

Vitaly

1 Answers

If you have the simplified data-set:

create table test_payment( payment_date, payment_sum ) AS
SELECT DATE '2020-01-01', 100 FROM DUAL UNION ALL
SELECT DATE '2021-01-01', 100 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 100 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 100 FROM DUAL UNION ALL
SELECT DATE '2023-01-01', 100 FROM DUAL UNION ALL
SELECT DATE '2024-01-01', 100 FROM DUAL UNION ALL
SELECT DATE '2020-01-01', 200 FROM DUAL UNION ALL
SELECT DATE '2021-01-01', 200 FROM DUAL UNION ALL
SELECT DATE '2024-01-01', 200 FROM DUAL UNION ALL
SELECT DATE '2023-01-01', 300 FROM DUAL;

Then:

select *
from (
  select extract(year from payment_date) AS year,
         payment_sum AS for_total,
         payment_sum
  from   test_payment
)
pivot (
  sum(payment_sum) for year in (2020,2021,2022,2023,2024)
);

Will PIVOT the year into columns and will GROUP BY all the columns that are not mentioned in the PIVOT clause, which is for_total, so it is the same as:

SELECT for_total,
       SUM(CASE year WHEN 2020 THEN payment_sum END) AS "2020",
       SUM(CASE year WHEN 2021 THEN payment_sum END) AS "2021",
       SUM(CASE year WHEN 2022 THEN payment_sum END) AS "2022",
       SUM(CASE year WHEN 2023 THEN payment_sum END) AS "2023",
       SUM(CASE year WHEN 2024 THEN payment_sum END) AS "2024"
FROM   (SELECT EXTRACT(year FROM payment_date) AS year,
               payment_sum AS for_total,
               payment_sum
        FROM   test_payment)
GROUP BY for_total;

Which both output:

FOR_TOTAL 2020 2021 2022 2023 2024
100 100 100 200 100 100
200 200 200 200
300 300

You can see that by having the FOR_TOTAL column, any values that appear multiple times across the years are totalled correctly in the relative years but the FOR_TOTAL value is the unique value and will not include duplicates; which is why you are under-counting.

You are effectively performing the calculation:

SELECT SUM(DISTINCT payment_sum) AS for_total
FROM   test_payment;

What you want to do is to not include FOR_TOTAL and then generate the total from the other columns:

SELECT "2020", "2021", "2022", "2023", "2024", 
       "2020" + "2021" + "2022" + "2023" + "2024" AS for_total
FROM   (
  SELECT EXTRACT(YEAR FROM payment_date) AS year,
         payment_sum
  FROM   test_payment
)
PIVOT (
  SUM(payment_sum) FOR year IN (2020,2021,2022,2023,2024)
);

Note: This will not total any dates outside the range of years in your pivot.

Which outputs:

2020 2021 2022 2023 2024 FOR_TOTAL
300 300 200 400 300 1500

Or:

SELECT SUM(CASE year WHEN 2020 THEN payment_sum END) AS "2020",
       SUM(CASE year WHEN 2021 THEN payment_sum END) AS "2021",
       SUM(CASE year WHEN 2022 THEN payment_sum END) AS "2022",
       SUM(CASE year WHEN 2023 THEN payment_sum END) AS "2023",
       SUM(CASE year WHEN 2024 THEN payment_sum END) AS "2024",
       SUM(payment_sum) AS for_total
FROM   (
  SELECT EXTRACT(YEAR FROM payment_date) AS year,
         payment_sum
  FROM   test_payment
  -- WHERE  payment_date >= DATE '2020-01-01'
  -- AND    payment_date < DATE '2025-01-01'
)

Note: This will total all dates, including those outside the range of years in your pivot. If you want to only total those within the range of years then uncomment the WHERE filters.

fiddle

like image 109
MT0 Avatar answered Aug 01 '26 11:08

MT0
Sign in to Comment

Related questions

How do you find the ID of the parent stored procedure within a nested procedure?
How to get id of just-executed sql query (in php)
inner join for a query?
Linq to SQL - Views vs Stored Procedures
Best way to store variable number of preferences?
How SELECT is executed before GROUP BY in this query?
what data type to use for storing different files in database
SQLSyntaxErrorException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported
SQL Server extra text after table name in SELECT

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!