Aggregating several columns in oracle sql

Question

Having a difficult time phrasing this question. Let me know if there's a better title.

I have a query that produces data like this:

+----------+----------+----------+----------+----------+
|   KEY    | FEB_GRP1 | JAN_GRP1 | FEB_GRP2 | JAN_GRP2 |
+----------+----------+----------+----------+----------+
| 50840992 |        1 |        1 |        0 |        0 |
| 50840921 |        0 |        1 |        1 |        0 |
| 50848995 |        0 |        0 |        0 |        0 |
+----------+----------+----------+----------+----------+

Alternatively, I can produce data like this:

+----------+------+------+
|   KEY    | JAN  | FEB  |
+----------+------+------+
| 50840992 | <50  | ~<50 |
| 50840921 | <50  | <50  |
| 50848995 | ~<50 | ~<50 |
| 50840885 | <50  | <50  |
+----------+------+------+

Where <50 should be counter as "group 1" and ~<50 should be counter as "group 2".

And I want it to be like this:

+-------+------+------+
| MONTH | GRP1 | GRP2 |
+-------+------+------+
| JAN   |    2 |    0 |
| FEB   |    1 |    1 |
+-------+------+------+

I can already get JAN_GRP1_SUM just by summing JAN_GRP1, but I want that to just be a data point, not a column itself.

My query (generates the first diagram):

SELECT *

FROM (
SELECT KEY, 
CASE WHEN "FEB-1-2016" = '<50' THEN 1 ELSE 0 END AS FEB_GRP1,
CASE WHEN "FEB-1-2016" != '<50' THEN 1 ELSE 0 END AS FEB_GRP2,
CASE WHEN "JAN-1-2016" = '<50' THEN 1 ELSE 0 END AS JAN_GRP1,
CASE WHEN "JAN-1-2016" != '<50' THEN 1 ELSE 0 END AS JAN_GRP2

FROM MY_TABLE);

This makes no sense "FEB-1-2016" = '<50' x 4 . Doublequote in Oracle is there to surround identifiers (ie tables and columns). You are surrounding a date. Maybe its a typo and you just wanted to use singlequote, but then you are comparing 2 hardcoded strings which is a non-sense too. Please explain. — Thomas G
– Thomas G, Commented Apr 20, 2016 at 17:12
I assume those are quoted identifiers in the table, and the values are strings. It would be helpful to show the table structure and the data that produced those results. I can duplicate it, but it's a strange table. Is it actually generated data from somewhere else? Sounds like it might be an X-Y problem, maybe... — Alex Poole
– Alex Poole, Commented Apr 20, 2016 at 17:14

Alex Poole · Accepted Answer · 2016-04-20 17:32:37Z

2

Your data model doesn't make much sense, but from what you've shown you can do:

select 'JAN' as month,
  count(case when "JAN-1-2016" = '<50' then 1 end) as grp1,
  count(case when "JAN-1-2016" != '<50' then 1 end) as grp2
from my_table
union all
select 'FEB' as month,
  count(case when "FEB-1-2016" = '<50' then 1 end) as grp1,
  count(case when "FEB-1-2016" != '<50' then 1 end) as grp2
from my_table;

That doesn't scale well - if you have more months you need to add another union branch for each one.

If your query is based on a view or a previously calculated summary then it will probably be much easier to go back to the original data.

If you are stuck with this then another possible approach, which might be more manageable if you actually have more than two months to look at, could be to unpivot the data:

select *
from my_table
unpivot(value for month in ("JAN-1-2016" as date '2016-01-01',
  "FEB-1-2016" as date '2016-02-01') --, etc. for other months
);

and then aggregate that:

select to_char(month, 'MON', 'NLS_DATE_LANGUAGE=ENGLISH') as month,
  count(case when value = '<50' then 1 end) as grp1,
  count(case when value != '<50' then 1 end) as grp2
from (
  select *
  from my_table
  unpivot(value for month in ("JAN-1-2016" as date '2016-01-01',
    "FEB-1-2016" as date '2016-02-01') --, etc. for other months
  )
)
group by month;

Still not pretty and Oracle is doing pretty much the same thing under the hood I think, but fewer case expressions to create and maintain - the drudge part is the unpivot pairs. You might need to include the year in the `'month' field, depending on the range of data you have.

edited Apr 20, 2016 at 17:32

answered Apr 20, 2016 at 17:23

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel Paczuski Bak Over a year ago

Awesome. What would I do if I wanted to unpivot based on year as well? My data actually spans more than a year (I was simplifying for the example problem).

Alex Poole Over a year ago

The unpivot keeps the year, and the grouping honours that too; you just need to include the year in the to_char format mask really?

Daniel Paczuski Bak Over a year ago

I don't understand what this means.

Alex Poole Over a year ago

@DanielPaczuskiBak - the unpivot clause is using the month and year, as in the date '2016-01-01' value. That means the month value it's grouping on is that same date, including the year. If your data spans a year you'd current see two rows for 'JAN' (or some months anyway). So I think all you need to do is change the first line to select to_char(month, 'MON YYYY', ... or however you want to display the year and month together.

Collectives™ on Stack Overflow

Aggregating several columns in oracle sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related