oracle sql adding extra row -
i have data 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
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 grouping getting more number of rows , please suggest solution.
edit
select col1, col2,col3,sum(nvl(detailtype,0)) tab rownum<10 group rollup(col1, col2,col3)
i have tried way original query is
select col1, col2,col3 tab rownum<10
this gives 9 rows when running above query getting 27 such rows thats not expected
you can use rollup in oralce.
check "rollup " in link http://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php same example posting :
select fact_1_id, fact_2_id, sum(sales_value) sales_value dimension_tab group rollup (fact_1_id, fact_2_id) order 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
Comments
Post a Comment