0

I have 2 queries:

select zam_klt_id,zam_order_date, count(*) as sum from orders
group by rollup (zam_order_date,zam_klt_id);

which produce output:

ZAM_KLT_ID ZAM_order_date        SUM
---------- ------------------- ----------
  1002 98/03/13                     1 
       98/03/13                     1 
  1004 98/03/14                     1 
       98/03/14                     1 
  1003 98/09/11                     1 
       98/09/11                     1 
  1003 99/01/05                     1 
       99/01/05                     1 
  1003 99/03/01                     1 
       99/03/01                     1 
  1003 99/07/26                     1 
       99/07/26                     1 
  1003 99/10/30                     1 
       99/10/30                     1 
  1002 00/05/08                     1 
       00/05/08                     1 
  1004 00/06/14                     1 
       00/06/14                     1 
       00/07/12                     1 
       00/07/12                     1 
  1000 00/12/10                     2 
       00/12/10                     2 
  1004 00/12/21                     1 
       00/12/21                     1 
                                   13

This is OK, under every date there is short summary (count) like in

  1000 00/12/10                     2 
       00/12/10                     2

However then I wanted to know in every year how many clients made orders, so I changed preveious query (zam_order_date was changed to to_char(zam_order_date,'yyyy'))

select zam_klt_id,to_char(zam_order_date,'yyyy'), count(*) as sum from orders
group by rollup ((to_char(zam_order_date,'yyyy'),zam_klt_id));

will produce output

ZAM_KLT_ID TO_CHAR(ZAM_order_date,'YYYY')        SUM
---------- ----------------------------------- ----------
  1002 1998                                         1 
  1003 1998                                         1 
  1004 1998                                         1 
  1003 1999                                         4 
       2000                                         1 
  1000 2000                                         2 
  1002 2000                                         1 
  1004 2000                                         2 
                                                   13 

9 rows selected

this time there is no summary under every date (year in this case), I think the output should look like this :

ZAM_KLT_ID TO_CHAR(ZAM_order_date,'YYYY')        SUM
---------- ----------------------------------- ----------
  1002 1998                                         1 
  1003 1998                                         1 
  1004 1998                                         1 
      *1998*                                         *3*
etc

Why summaries are not added this time, is it have something to do with the to_char function?

new_robo
  • 1
  • If you try your query using ALL_OBJECTS instead, do you get a similar result? Works for me in 11g: select object_id, to_char(created,'yyyy') as year, count(*) as sum from all_objects group by rollup (to_char(created,'yyyy'), object_id) – Patrick Marchand Aug 15 '13 at 15:56
  • Yes, the same result is produced, looks like this: 1400 2013 1 1500 2013 1 1600 2013 1 1700 2013 1 – new_robo Aug 15 '13 at 17:10
  • Odd. Ok, to see if it's a datatype issue, replace TO_CHAR with TRUNC. You'll get a full date showing up in the output, but the month and day will always be Jan 1: TO_CHAR(zam_order_date, 'yyyy') ==> TRUNC(zam_order_date, 'yyyy') and see if you get the subtotals as expected. (That is, in your example, you should have a subtotal for Jan 1, 1998, Jan 1, 1999, and Jan 1, 2000 and internally Oracle will treat them as dates, not character values.) – Patrick Marchand Aug 15 '13 at 17:43
  • after this change subtotals are identical (only dates are now presented as you wrote) 1002 98/01/01 1 1003 98/01/01 1 1004 98/01/01 1 1003 99/01/01 4 00/01/01 1 1000 00/01/01 2 1002 00/01/01 1 1004 00/01/01 2 13 – new_robo Aug 15 '13 at 18:05
  • Unfortunately I don't have a 10g db to test with, but what if you tried a slightly different query using GROUPING SETS instead of ROLLUP: select object_id, to_char(created,'yyyy') as year, count(*) as sum from all_objects group by grouping sets (to_char(created,'yyyy'), object_id) , (to_char(created,'yyyy')) – Patrick Marchand Aug 16 '13 at 19:20

0 Answers0