2

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.

4

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.)

  • 2
    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. – ypercubeᵀᴹ Mar 19 '17 at 20:19
3

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

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.