By Wilson


2017-03-18 20:23:06 8 Comments

This question is similar, yet different, to another DBA SE question I have here: Group by NAME and YEAR, categorize NAME


I have a FINANCE_SOURCE table:

+-----------+------------+-------------+--------+------+
| SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR |
+-----------+------------+-------------+--------+------+
|       101 |          1 | A           |  10.00 | 2017 |
|       102 |          1 | B           |   5.00 | 2017 |
|       103 |          1 | B           |  15.00 | 2017 |
|       104 |          1 | B           |  70.00 | 2016 |
|       105 |          1 | C           |  30.00 | 2017 |
|       106 |          1 | D           |   1.00 | 2016 |
|       107 |          1 | D           |  20.00 | 2017 |
+-----------+------------+-------------+--------+------+

I want to rearrange the data, so that SOURCE_A, SOURCE_B, and OTHER(anything that's not A or B) are in separate columns.

The data needs to be grouped into separate YEARS:

+------------+---------+---------+-------------+-------+------+
| PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR |
+------------+---------+---------+-------------+-------+------+
|          1 |      10 |      20 |          50 |    80 | 2017 |
|          1 |         |      70 |           1 |    71 | 2016 |
+------------+---------+---------+-------------+-------+------+

How can I do this? Performance is important.

3 comments

@McNets 2017-03-18 20:29:06

You can get it by using SUM(CASE...) statement.

SELECT   PROJECT_ID,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME NOT IN ('A','B') THEN AMOUNT END), 0) OTHER_TOTAL,
         COALESCE(SUM(AMOUNT), 0) AS TOTAL,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID, YEAR;
PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |       0 |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

dbfiddle here

As @JackDouglas has suggested, let me add his new solution that could be slightly quicker.

SELECT PROJECT_ID
     , A_TOTAL
     , B_TOTAL
     , nvl(TOTAL,0)-nvl(A_TOTAL,0)-nvl(B_TOTAL,0) OTHER_TOTAL
     , TOTAL
     , YEAR
FROM( SELECT   PROJECT_ID,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
               COALESCE(SUM(AMOUNT), 0) AS TOTAL,
               YEAR
      FROM     FINANCE_SOURCE
      GROUP BY PROJECT_ID, YEAR );

dbfiddle here
(Execution plan included.)

@ypercubeᵀᴹ 2017-03-19 20:19:29

The ELSE 0 will give 0 when there is nothing to sum up. Otherwise, all nulls will sum up to NULL and you'll need COALESCE() or similar to convert to 0. If the OP prefers null in those cases, it's fine to remove the ELSE 0 of course.

@Jack says try topanswers.xyz 2017-03-19 20:56:51

Apart from the traditional SUM(CASE), there are two other ways of getting this sort of PIVOT output.

The first is using the PIVOT and UNPIVOT operators introduced in 11.1:

select project_id
     , a_total
     , b_total
     , other_total
     , nvl(a_total,0)+nvl(b_total,0)+nvl(other_total,0) total
     , year
from ( select project_id
            , decode(source_name,'A','A','B','B','O') src
            , amount
            , year
       from finance_source )
pivot (sum(amount) for src in ('A' a_total, 'B' b_total, 'O' other_total));
PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |    null |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

The second is using the MODEL clause introduced in 10.1:

select project_id, a_total, b_total, other_total, total, year
from finance_source
model unique single reference
return updated rows
partition by (project_id, year)
dimension by (source_name)
measures (amount, 0 a_total, 0 b_total, 0 other_total, 0 total)
rules upsert
(
  a_total[1] = sum(amount)['A']
, b_total[1] = sum(amount)['B']
, other_total[1] = nvl(sum(amount)[any],0)-nvl(sum(amount)['B'],0)-nvl(sum(amount)['A'],0)
, total[1] = sum(amount)[any]
);
PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |    null |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

In most simple cases I would expect the PIVOT to be faster (and about the same as the SUM(CASE)), but if your real requirements are more complex, you may find the MODEL clause a better fit — it covers a lot of more complex situations too.

dbfiddle here

@Evan Carroll 2017-03-19 03:13:14

This isn't quite what you want, but you can always use GROUPING SETS too. I used PostgreSQL for the demo but it should work the same w/ Oracle.

Sample data

CREATE TABLE foo
AS 
  SELECT *
  FROM ( VALUES
    ( 101, 1, 'A', 10.00, 2017 ),
    ( 102, 1, 'B',  5.00, 2017 ),
    ( 103, 1, 'B', 15.00, 2017 ),
    ( 104, 1, 'B', 70.00, 2016 ),
    ( 105, 1, 'C', 30.00, 2017 ),
    ( 106, 1, 'D',  1.00, 2016 ),
    ( 107, 1, 'D', 20.00, 2017 )
  ) AS t(source_id, project_id, source_name, amount, year);

GROUPING SETS

SELECT
  project_id,
  year,
  CASE
    WHEN source_name = 'A' THEN 'A'
    WHEN source_name = 'B' THEN 'B'
    ELSE 'OTHER'
  END AS source,
  sum(amount)
FROM foo
GROUP BY GROUPING SETS(
  (
    project_id,
    year,
    CASE
      WHEN source_name = 'A' THEN 'A'
      WHEN source_name = 'B' THEN 'B'
      ELSE 'OTHER' END
  ),
  (project_id, year)
)
ORDER BY 1, 2, 3;


 project_id | year | source |  sum  
------------+------+--------+-------
          1 | 2016 | B      | 70.00
          1 | 2016 | OTHER  |  1.00
          1 | 2016 |        | 71.00
          1 | 2017 | A      | 10.00
          1 | 2017 | B      | 20.00
          1 | 2017 | OTHER  | 50.00
          1 | 2017 |        | 80.00
(7 rows)

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

Change Order in Pivot Table

  • 2019-04-25 10:09:30
  • Błażej
  • 34 View
  • 0 Score
  • 1 Answer
  • Tags:   oracle pivot

1 Answered Questions

[SOLVED] Join tables and pivot

  • 2018-06-27 12:51:19
  • Anand Shah
  • 1011 View
  • 2 Score
  • 1 Answer
  • Tags:   mysql pivot

1 Answered Questions

[SOLVED] Summary statistics: Multiple joins and group by

2 Answered Questions

[SOLVED] Categorize rows as first, last or in between

1 Answered Questions

[SOLVED] using PIVOT for a table in ORACLE 12C

1 Answered Questions

[SOLVED] Pivot table with MySQL

  • 2014-08-29 21:44:56
  • syntaxticallychallenged
  • 2393 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql group-by pivot

Sponsored Content