Home » SQL & PL/SQL » SQL & PL/SQL » group by/rollup? (oracle 9.2)
group by/rollup? [message #311405] Fri, 04 April 2008 07:06 Go to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi all,

I seem to be unable to use group by rollup/grouping/grouping sets, etc. in a proper way - frustrating. I must be overlooking something in this case and I hope you can help me!

Data is (simplified):
exp_id project_id task_id expenditure_date costs
1      1          1       1-JAN-2008        200
2      1          1       1-JAN-2008         50
3      1          1       2-JAN-2008        100
4      1          2       1-JAN-2008        250
5      1          2       1-JAN-2008        100


Desired result is:
project_id task_id expenditure_date costs
1          1       1-JAN-2008        250 --> sum of id 1 and 2
1          1       2-JAN-2008        100
1          2       1-JAN-2008        350 --> sum of id 4 and 5
1          0       1-JAN-2008        500 --> sum of id 1,2,4 and 5

So: total costs per project, per date, per task and total costs per project, per task (and task_id showing as 0 for that sum row).

What I tried:
SQL> SELECT peia.project_id
  2        ,peia.task_id
  3        ,peia.org_id
  4        ,peia.expenditure_item_date
  5        ,SUM(peia.burden_cost) burden_cost
  6  FROM   pa_expenditure_items_all peia
  7  WHERE  peia.org_id = 1585
  8  GROUP  BY GROUPING SETS((peia.project_id, peia.org_id, peia.expenditure_item_date))
  9  /

SELECT peia.project_id
      ,peia.task_id
      ,peia.org_id
      ,peia.expenditure_item_date
      ,SUM(peia.burden_cost) burden_cost
FROM   pa_expenditure_items_all peia
WHERE  peia.org_id = 1585
GROUP  BY GROUPING SETS((peia.project_id, peia.org_id, peia.expenditure_item_date))

ORA-00979: not a GROUP BY expression


Any suggestions will be highly appreciated!

Re: group by/rollup? [message #311420 is a reply to message #311405] Fri, 04 April 2008 08:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Can you rebuild your sample sample data ptovided as you have made some errors whilst 'simplifying'
Different column names to those used in your query,
Columns metioned in the query but not in the example desired result set, innaccurate calculations (sum of 1,2, 4 and 5 would be 600 not 500) no explanation as to why trans 3 is not included in the total.
The reason for the error that you are currentky getting is because you have included a column in the select clause that has not been included in the group by clause.
When you repost your data, please do it in the form of insert statements (along with a create table statement) and rework your desired output to accurately reflect the sample data.
Cheers
Re: group by/rollup? [message #311439 is a reply to message #311420] Fri, 04 April 2008 08:53 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sorry, retry:
create table TEST_X
(
  EXP_ID           NUMBER,
  PROJECT_ID       NUMBER,
  TASK_ID          NUMBER,
  EXPENDITURE_DATE DATE,
  COSTS            NUMBER
)
;
insert into TEST_X (EXP_ID, PROJECT_ID, TASK_ID, EXPENDITURE_DATE, COSTS)
values (1, 1, 1, to_date('01-01-2008', 'dd-mm-yyyy'), 200);
insert into TEST_X (EXP_ID, PROJECT_ID, TASK_ID, EXPENDITURE_DATE, COSTS)
values (2, 1, 1, to_date('01-01-2008', 'dd-mm-yyyy'), 50);
insert into TEST_X (EXP_ID, PROJECT_ID, TASK_ID, EXPENDITURE_DATE, COSTS)
values (3, 1, 1, to_date('02-01-2008', 'dd-mm-yyyy'), 100);
insert into TEST_X (EXP_ID, PROJECT_ID, TASK_ID, EXPENDITURE_DATE, COSTS)
values (4, 1, 2, to_date('01-01-2008', 'dd-mm-yyyy'), 250);
insert into TEST_X (EXP_ID, PROJECT_ID, TASK_ID, EXPENDITURE_DATE, COSTS)
values (5, 1, 2, to_date('01-01-2008', 'dd-mm-yyyy'), 100);
commit;


Desired result:
project_id task_id expenditure_date costs
1          1	   1-1-2008	    250
1	   1	   2-1-2008	    100
1	   2	   1-1-2008	    350
1	   0	   1-1-2008	    600
1	   0	   2-1-2008	    100


Tried:
SQL> SELECT t.project_id
  2        ,t.task_id
  3        ,t.expenditure_date
  4        ,SUM(t.costs) costs
  5  FROM   test_x t
  6  GROUP  BY GROUPING SETS((t.project_id, t.expenditure_date))
  7  /

SELECT t.project_id
      ,t.task_id
      ,t.expenditure_date
      ,SUM(t.costs) costs
FROM   test_x t
GROUP  BY GROUPING SETS((t.project_id, t.expenditure_date))

ORA-00979: not a GROUP BY expression


And I know that the error means that I should include task_id in the group by expression, but that's exactly the point: I don't want to, because I want aggregates for both task (detailed) level and on project/date level.
Re: group by/rollup? [message #311442 is a reply to message #311439] Fri, 04 April 2008 09:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I'm no expert, but I reckon this'll do it (for your current resultset anyways)

SELECT t.project_id
          ,NVL(t.task_id,0)
          ,t.expenditure_date
          ,SUM(t.costs) costs
    FROM   test_x t
    GROUP  BY GROUPING SETS((project_id, expenditure_date),(t.project_id, T.TASK_ID, t.expenditure_date))
    order by task_id nulls last, expenditure_date
    /
Re: group by/rollup? [message #311444 is a reply to message #311442] Fri, 04 April 2008 09:06 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Aha! What I missed so far is that the "complete" grouping set (so, all columns, in this example project_id, task_id and expenditure_date) didn't cause aggregated lines, but just returned the data itself. I didn't think of including all columns as a grouping set.

Hope this post makes sense to anyone... Anyway, Pablolee, thanks!!
Re: group by/rollup? [message #311476 is a reply to message #311444] Fri, 04 April 2008 10:49 Go to previous messageGo to next message
Bhavin Adhvaryu
Messages: 3
Registered: April 2003
Junior Member
This is great. But what happens if number of column grows say 30. Then how would you write the query. There should be some way of writing good query with 30 columns.

Please help. I have same requirement but no of columns are 30.
How can I do this? Would it possible using Analytic Function.
Re: group by/rollup? [message #311481 is a reply to message #311476] Fri, 04 April 2008 11:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's wrong with typing 30 column names?
Re: group by/rollup? [message #311483 is a reply to message #311481] Fri, 04 April 2008 11:07 Go to previous messageGo to next message
Bhavin Adhvaryu
Messages: 3
Registered: April 2003
Junior Member
Good programmer would like to write 100 column names?? No!! There should be some way??
Re: group by/rollup? [message #311491 is a reply to message #311439] Fri, 04 April 2008 11:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am not sure this will satisfy for all of your records. But it works for the example you have posted here.

SQL> select * from test_x;

    EXP_ID PROJECT_ID    TASK_ID EXPENDITURE_DATE        COSTS
---------- ---------- ---------- ------------------ ----------
         1          1          1 01-JAN-08                 200
         2          1          1 01-JAN-08                  50
         3          1          1 02-JAN-08                 100
         4          1          2 01-JAN-08                 250
         5          1          2 01-JAN-08                 100
SQL> l
  1* select * from test_x
SQL> select case when grouping_id(project_id) = 1 then 999 else project_id end project_id,
  2         case when grouping_id(task_id) = 1 then 0 else task_id end task_id,
       expenditure_date,
  3    4         sum(costs) from test_x
  5  group by rollup((project_id, expenditure_date), task_id)
  6  order by project_id;

PROJECT_ID    TASK_ID EXPENDITURE_DATE   SUM(COSTS)
---------- ---------- ------------------ ----------
         1          1 01-JAN-08                 250
         1          2 01-JAN-08                 350
         1          0 01-JAN-08                 600
         1          1 02-JAN-08                 100
         1          0 02-JAN-08                 100
       999          0                           700

Hope this helps

Regards

Raj
Re: group by/rollup? [message #311495 is a reply to message #311483] Fri, 04 April 2008 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Good programmer would like to write 100 column names

Good programmer writes what he has to write and doesn't care about the number of characters he has to type.
What a stupid way to think.

Regards
Michel

Re: group by/rollup? [message #311527 is a reply to message #311491] Fri, 04 April 2008 12:36 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This is another option.
SQL> select project_id, task_id, expenditure_date, sum_costs
  2  from
  3  test_x
  4  group by project_id, task_id, expenditure_date
  5  model
  6  dimension by (project_id as project_id, task_id as task_id, expenditure_date as expenditure_date)
  7  measures (sum(costs) as sum_costs, 0 as proj_task_sum)
  8  rules upsert all
  9  (
 10    sum_costs[project_id,0,expenditure_date] = sum(sum_costs)[cv(),any,cv()]
 11  );

PROJECT_ID    TASK_ID EXPENDITURE_DATE    SUM_COSTS
---------- ---------- ------------------ ----------
         1          1 01-JAN-08                 250
         1          1 02-JAN-08                 100
         1          2 01-JAN-08                 350
         1          0 01-JAN-08                 600
         1          0 02-JAN-08                 100

Regards

Raj

P.S : This works only from 10g onwards.

[Updated on: Fri, 04 April 2008 13:57]

Report message to a moderator

Previous Topic: can we use LIKE and BETWEEN same time
Next Topic: Order By length of RecName field...is it possible? (merged)
Goto Forum:
  


Current Time: Sat Dec 03 01:29:08 CST 2016

Total time taken to generate the page: 0.05088 seconds