oracle sql adding extra row

Question

I have data like this

col1            col2                                col3        col3  
ABC COMPANY     OH, Akron - 3636 Copley Rd           $50.00     MMO64606
ABC COMPANY     OH, Akron - 3636 Copley Rd           $200.00    MMO64606
ABC COMPANY     OH, Akron - 3636 Copley Rd           $100.00    MMO64606
ABC COMPANY     OH, Beachwood - 23700 Commerce Park  $20.00     MMO64606
ABC COMPANY     OH, Beachwood - 23700 Commerce Park  $200.00    MMO64606
ABC COMPANY     OH, Beachwood - 23700 Commerce Park  $1,000.00  MMO64606

I need output to be like

col1            col2                                col3        col3  
ABC COMPANY     OH, Akron - 3636 Copley Rd           $50.00     MMO64606
ABC COMPANY     OH, Akron - 3636 Copley Rd           $200.00    MMO64606
ABC COMPANY     OH, Akron - 3636 Copley Rd           $100.00    MMO64606
                OH, Akron - 3636 Copley Rd Total     $350.00    
ABC COMPANY     OH, Beachwood - 23700 Commerce Park  $20.00     MMO64606
ABC COMPANY     OH, Beachwood - 23700 Commerce Park  $200.00    MMO64606
ABC COMPANY     OH, Beachwood - 23700 Commerce Park  $1,000.00  MMO64606
                OH, Beachwood - 23700 Commerce Park Total   $1,220.00

I have used rollup but its grouping I am getting more number of rows , please suggest any solution.

EDIT

select col1, col2,col3,sum(nvl(detailtype,0))
  from tab
  where rownum<10
  group by
  rollup(col1, col2,col3)

I have tried this way my original query is

select col1, col2,col3
  from tab
  where rownum<10

this gives only 9 rows when i am running the above query i am getting 27 such rows but thats not expected

select col1, col2,col3,sum(nvl(detailtype,0)) from tab where rownum<10 group by rollup(col1, col2,col3) ; I have tried this way my original query is select col1, col2,col3,from tab where rownum<10; this gives only 9 rows when i am running the above query i am getting 27 such rows but thats not expected — nitish rao, Apr 25 '15 at 09:34
When adding information to a question do not put it in a comment; instead, use the `edit` button (location just under the tags) to add the new information to the question itself so everyone can see it when they read the question. I've done it for you this time. Thanks. — Bob Jarvis - Слава Україні, Apr 25 '15 at 11:15

score 1 · Answer 1 · answered Apr 25 '15 at 08:28

you can use ROLLUP in oralce.

check "ROLLUP " in this link http://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php same example i am posting :

SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

 FACT_1_ID  FACT_2_ID SALES_VALUE
---------- ---------- -----------
         1          1     4363.55
         1          2     4794.76
         1          3     4718.25
         1          4     5387.45
         1          5     5027.34
         1               24291.35
         2          1     5652.84
         2          2     4583.02
         2          3     5555.77
         2          4     5936.67
         2          5     4508.74
         2               26237.04
                         50528.39

Ponder Stibbons · Answer 2 · 2015-04-25T10:54:13.040

You can use union all with partial sums:

select col1, col2, col3, col4 
from test 
union all
select null, col2, sum(col3), null 
from test
group by col2   
order by col2, col1

SQLFiddle demo

or use cube and grouping_id:

select col1, col2, sum(col3) col3, col4
from test 
group by cube(col1, col2, col3, col4)
having grouping_id(col1, col2, col3, col4) in (0, 11)

SQLFiddle demo

Edit: in first query union all is correct, not union

oracle sql adding extra row

EDIT

2 Answers2