Home » SQL & PL/SQL » SQL & PL/SQL » Column total (11g)
Column total [message #441261] Fri, 29 January 2010 22:27 Go to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Out of following query is 37 rows, i want to include last row as column totals can you guide me....... For first two columns totals are not required, i do not want to use compute statement

SELECT c.sr_no, c.NAME, c.employer_target, b.employers_registered,

c.employees_target, b.employees_registered, c.contribution_target,

a.contribution, c.arrear_target, a.arrears, a.total_contr,

b.pr02a_target, c.pr02a_entered

FROM t_set1 c, t_set3 a, t_set2 b

WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1

ORDER BY 1;
Re: Column total [message #441270 is a reply to message #441261] Sat, 30 January 2010 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One way is:
SQL> break on report
SQL> compute sum label Total of sal on report
SQL> select ename, sal from emp order by ename;
ENAME             SAL
---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JONES            2975
KING             5000
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250
           ----------
Total           29025

14 rows selected.

Regards
Michel

[Updated on: Sat, 30 January 2010 01:35]

Report message to a moderator

Re: Column total [message #441272 is a reply to message #441261] Sat, 30 January 2010 01:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
In my opinion, displaying totals is client's job, so using SQL*Plus COMPUTE statement (or its equivalent in other tools) is the best option.

From pure SQL, the most "understandable" way is using UNION ALL with the row with computed SUMS.
This is also achievable by using enhanced aggregation options, as described e.g. in the Enhanced Aggregation, Cube, Grouping and Rollup article. Just follow the link. Concentrate on GROUPING SETS or ROLLUP/GROUPING_ID description. You have to aggregate in this case; if the first two columns may be duplicate (which you did not post), you would have to GROUP BY something unique (e.g. ROWNUM pseudocolumn).
Re: Column total [message #441274 is a reply to message #441261] Sat, 30 January 2010 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way is:
SQL> select decode(grouping(ename),1,'Total',ename) ename,
  2         sum(sal) sal
  3  from emp
  4  group by rollup(ename)
  5  /
ENAME             SAL
---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JONES            2975
KING             5000
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250
Total           29025

15 rows selected.

Regards
Michel
Re: Column total [message #441279 is a reply to message #441274] Sat, 30 January 2010 02:19 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Thanks, in this example one column total is possible, but i have to take totals of multiple columns in last row, how i can do....
Re: Column total [message #441281 is a reply to message #441279] Sat, 30 January 2010 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

Regards
Michel
Re: Column total [message #441285 is a reply to message #441281] Sat, 30 January 2010 03:53 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Here is script with sample data


CREATE TABLE CORE_BUSINESS.RGN_ACHIVEMENTS

(

SR_NO NUMBER,

NAME VARCHAR2(25 BYTE) NOT NULL,

EMPLOYER_TARGET NUMBER(5),

EMPLOYERS_REGISTERED NUMBER,

EMPLOYEES_TARGET NUMBER(5),

EMPLOYEES_REGISTERED NUMBER,

CONTRIBUTION_TARGET NUMBER(12,2),

CONTRIBUTION NUMBER,

ARREAR_TARGET NUMBER(12,2),

ARREARS NUMBER,

TOTAL_CONTR NUMBER,

PR02A_TARGET NUMBER,

PR02A_ENTERED NUMBER

);



Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(1, 'Nazimabad', 394, 85, 8410,

1910, 480000000, 185588488, 17000000, 19142338,

204730826, 187557, 1314);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(2, 'Karimabad', 415, 170, 4650,

4584, 266000000, 104412012, 32000000, 11346600,

115758612, 52978, 3221);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(3, 'City', 436, 106, 6520,

18125, 407000000, 212407494.78, 13000000, 2172351.14,

214579845.92, 113310, 1453);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(4, 'West Wharf', 353, 30, 9930,

13100, 567000000, 248728469, 6000000, 6063705.64,

254792174.64, 169426, 1240);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(5, 'Karachi Central', 330, 38, 4130,

3622, 236000000, 123016640, 10000000, 4355696,

127372336, 72725, 4097);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(6, 'Korangi', 375, 83, 7440,

6977, 390000000, 210851063, 20000000, 12563196,

223414259, 112271, 2096);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(7, 'Bin Qasim', 182, 36, 7170,

3629, 410000000, 164230628, 2000000, 5382063,

169612691, 120269, 11079);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(8, 'Kotri', 128, 31, 1840,

2901, 169000000, 75866139, 4000000, 2405146,

78271285, 123718, 4933);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(9, 'Hyderabad', 172, 39, 1730,

2387, 122000000, 60801747, 9000000, 5374314,

66176061, 102200, 312);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(10, 'Sukkur', 174, 76, 1640,

985, 75000000, 37807433, 11000000, 3801934,

41609367, 29337, 1508);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(11, 'Muzaffargarh', 93, 24, 1760,

791, 113000000, 42076712, 7000000, 492255,

42568967, 88945, 61);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(12, 'Multan', 232, 65, 4260,

1719, 224000000, 95997303, 9000000, 741226,

96738529, 77511, 501);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(13, 'Sahiwal', 41, 30, 3170,

520, 77000000, 13700652, 2000000, 9560,

13710212, 32708, NULL);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(14, 'Bahawalpur', 169, 128, 1810,

1906, 109000000, 50230405, 18000000, 2134213,

52364618, 48842, 2114);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(15, 'Faisalabad Centeral', 345, 50, 2510,

680, 141000000, 46743255, 6000000, 451306,

47194561, 22895, 14);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(16, 'Faisalabad South', 259, 68, 3430,

6428, 192000000, 99824683, 22000000, 755685,

100580368, 48621, 642);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(17, 'Faisalabad North', 285, 57, 5120,

10545, 287000000, 137395705.38, 21000000, 717389,

138113094.38, 69909, 1272);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(18, 'Sargodha', 131, 36, 1970,

794, 94000000, 38358860, 6000000, 625323,

38984183, 69524, 1216);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(19, 'Lahore South', 405, 101, 5890,

10000, 365000000, 207980162, 20000000, 2291699.43,

210271861.43, 124035, 9176);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(20, 'Mangamandi', 207, 55, 9120,

4111, 512000000, 212887714, 15000000, 1143193.8,

214030907.8, 98003, 268);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(21, 'Lahore Centeral', 384, 135, 4480,

4187, 277000000, 124372672.36, 18000000, 1411693,

125784365.36, 91346, 4732);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(22, 'Shahdara', 95, 48, 2390,

858, 140000000, 48475060, 2000000, 379638,

48854698, 42935, NULL);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(23, 'Lahore North', 538, 130, 6840,

7031, 421000000, 214076589, 7000000, 1524612.8,

215601201.8, 105519, NULL);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(24, 'Sheikhupura', 63, 37, 1590,

2065, 297000000, 125485619, 12000000, 2852477,

128338096, 100403, 1247);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(25, 'Gujranwala', 147, 48, 2600,

650, 177000000, 72930245.69, 6000000, 675090,

73605335.69, 59437, 34);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(26, 'Gujrat', 120, 15, 1520,

504, 75000000, 32232591, 5000000, 428525,

32661116, 33190, 713);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(27, 'Sialkot', 232, 100, 3080,

2150, 177000000, 70255004.6, 9000000, 2075673,

72330677.6, 37742, 678);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(28, 'Jehlum', 90, 38, 1610,

1324, 80000000, 33366358, 4000000, 2192451,

35558809, 39290, 3150);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(29, 'Rawalpindi', 451, 75, 4980,

5417, 183000000, 104970732, 1000000, 1104539,

106075271, 89967, 9524);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(30, 'Islamabad', 201, 80, 7010,

9888, 356000000, 188839282, 12000000, 5388175,

194227457, 80805, 2188);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(31, 'Hasanabdal', 145, 29, 1660,

348, 58000000, 23654085, 2000000, 304324,

23958409, 36361, 318);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(32, 'Peshawar', 299, 108, 3130,

2951, 136000000, 66678676, 4000000, 1191715,

67870391, 65410, 3546);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(33, 'Mardan', 157, 43, 2020,

1514, 100000000, 41430837, 4000000, 2930836,

44361673, 60213, 1774);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(34, 'Abbottabad', 180, 52, 2060,

2149, 101000000, 43288123, 22000000, 1967729,

45255852, 47404, 2437);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(35, 'Gilgit', 26, 14, 280,

163, 17000000, 8484357, 1000000, 36834,

8521191, 2796, 1475);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(36, 'Quetta', 78, 15, 1060,

583, 64000000, 24009262, 3000000, 765418,

24774680, 33626, 54);

Insert into CORE_BUSINESS.RGN_ACHIVEMENTS

(SR_NO, NAME, EMPLOYER_TARGET, EMPLOYERS_REGISTERED, EMPLOYEES_TARGET,

EMPLOYEES_REGISTERED, CONTRIBUTION_TARGET, CONTRIBUTION, ARREAR_TARGET, ARREARS,

TOTAL_CONTR, PR02A_TARGET, PR02A_ENTERED)

Values

(37, 'Hub', 68, 45, 1190,

828, 89000000, 45081727, 3000000, 2510129,

47591856, 30756, 1604);

Re: Column total [message #441288 is a reply to message #441285] Sat, 30 January 2010 04:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not only do the extra blank lines make your post extra long, it will also raise errors in sqlplus.

[Updated on: Sat, 30 January 2010 04:04]

Report message to a moderator

Re: Column total [message #441290 is a reply to message #441285] Sat, 30 January 2010 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what should be the result with these data?
In another words what do you want to sum?

Regards
Michel

[Updated on: Sat, 30 January 2010 04:08]

Report message to a moderator

Re: Column total [message #441292 is a reply to message #441261] Sat, 30 January 2010 04:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As you stated in your first post malhi wrote on Sat, 30 January 2010 05:27
can you guide me.......
and you were given link to article on this site describing possible ways of doing it and an example of ROLLUP clause, I just wonder, why you did not investigate it further.
What is the problem with understanding the article to which I posted the link?
What is the problem with reading details about ROLLUP in SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/?

ROLLUP clause may be used with multiple columns; but then it also produces some sub-totals. If you do not want them, you may get rid of them by filtering on GROUPING_ID value.
Although, I would rather use GROUPING SETS clause in this case.
Re: Column total [message #441409 is a reply to message #441292] Sun, 31 January 2010 22:24 Go to previous messageGo to next message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
I have done it by this way...........

SELECT c.employer_target "cal1", b.employers_registered "cal2",

c.employees_target "cal3", b.employees_registered "cal4",

c.contribution_target "cal5", a.contribution "cal6",

c.arrear_target "cal7", a.arrears "cal8", a.total_contr "cal9",

b.pr02a_target "cal10", c.pr02a_entered "cal11"

FROM core_business.t_set1 c, core_business.t_set3 a, core_business.t_set2 b

WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1

UNION

SELECT SUM (cal1) "cal1", SUM (cal2) "cal2", SUM (cal3) "cal3",

SUM (cal4) "cal4", SUM (cal5) "cal5", SUM (cal6) "cal6",

SUM (cal7) "cal7", SUM (cal8) "cal8", SUM (cal9) "cal9",

SUM (cal10) "cal10", SUM (cal11) "cal11"

FROM (SELECT c.employer_target cal1, b.employers_registered cal2,

c.employees_target cal3, b.employees_registered cal4,

c.contribution_target cal5, a.contribution cal6,

c.arrear_target cal7, a.arrears cal8, a.total_contr cal9,

b.pr02a_target cal10, c.pr02a_entered cal11

FROM core_business.t_set1 c,

core_business.t_set3 a,

core_business.t_set2 b

WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1

ORDER BY 1)
Re: Column total [message #441410 is a reply to message #441409] Sun, 31 January 2010 22:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

SELECT c.employer_target      "cal1",
       b.employers_registered "cal2",
       c.employees_target     "cal3",
       b.employees_registered "cal4",
       c.contribution_target  "cal5",
       a.contribution         "cal6",
       c.arrear_target        "cal7",
       a.arrears              "cal8",
       a.total_contr          "cal9",
       b.pr02a_target         "cal10",
       c.pr02a_entered        "cal11"
FROM   core_business.t_set1 c,
       core_business.t_set3 a,
       core_business.t_set2 b
WHERE  c.rgn = a.rgn4
       AND c.rgn = b.rgn1
UNION
SELECT Sum(cal1)  "cal1",
       Sum(cal2)  "cal2",
       Sum(cal3)  "cal3",
       Sum(cal4)  "cal4",
       Sum(cal5)  "cal5",
       Sum(cal6)  "cal6",
       Sum(cal7)  "cal7",
       Sum(cal8)  "cal8",
       Sum(cal9)  "cal9",
       Sum(cal10) "cal10",
       Sum(cal11) "cal11"
FROM   (SELECT   c.employer_target      cal1,
                 b.employers_registered cal2,
                 c.employees_target     cal3,
                 b.employees_registered cal4,
                 c.contribution_target  cal5,
                 a.contribution         cal6,
                 c.arrear_target        cal7,
                 a.arrears              cal8,
                 a.total_contr          cal9,
                 b.pr02a_target         cal10,
                 c.pr02a_entered        cal11
        FROM     core_business.t_set1 c,
                 core_business.t_set3 a,
                 core_business.t_set2 b
        WHERE    c.rgn = a.rgn4
                 AND c.rgn = b.rgn1
        ORDER BY 1) 
Re: Column total [message #441417 is a reply to message #441409] Sun, 31 January 2010 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this an answer or a question?

If this is a question:
Michel Cadot wrote on Sat, 30 January 2010 11:07
And what should be the result with these data?
In another words what do you want to sum?

Tell us with WORDS not with a query that we do not know if it needs your needs or not.

If this is an ansser, you can replace it in the same way with the 2 methods I gave.

Regards
Michel

[Updated on: Sun, 31 January 2010 23:31]

Report message to a moderator

Re: Column total [message #441421 is a reply to message #441410] Mon, 01 February 2010 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col name format a19
SQL> break on report
SQL> compute sum label total of c1 on report
SQL> compute sum label total of c2 on report
SQL> compute sum label total of c3 on report
SQL> compute sum label total of c4 on report
SQL> compute sum label total of c5 on report
SQL> compute sum label total of c6 on report
SQL> compute sum label total of c7 on report
SQL> compute sum label total of c8 on report
SQL> compute sum label total of c9 on report
SQL> compute sum label total of c10 on report
SQL> compute sum label total of c11 on report
SQL> select NAME
  2         ,  EMPLOYER_TARGET            c1
  3         , EMPLOYERS_REGISTERED       c2
  4         , EMPLOYEES_TARGET           c3
  5         , EMPLOYEES_REGISTERED       c4
  6         , CONTRIBUTION_TARGET        c5
  7         , CONTRIBUTION               c6
  8         , ARREAR_TARGET              c7
  9         , ARREARS                    c8 /*
 10         , TOTAL_CONTR                c9 
 11         , PR02A_TARGET               c10
 12         , PR02A_ENTERED              c11 */
 13  from RGN_ACHIVEMENTS
 14  /
NAME                        C1         C2         C3         C4         C5         C6         C7         C8
------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Nazimabad                  394         85       8410       1910  480000000  185588488   17000000   19142338
Karimabad                  415        170       4650       4584  266000000  104412012   32000000   11346600
City                       436        106       6520      18125  407000000  212407495   13000000 2172351.14
West Wharf                 353         30       9930      13100  567000000  248728469    6000000 6063705.64
Karachi Central            330         38       4130       3622  236000000  123016640   10000000    4355696
Korangi                    375         83       7440       6977  390000000  210851063   20000000   12563196
Bin Qasim                  182         36       7170       3629  410000000  164230628    2000000    5382063
Kotri                      128         31       1840       2901  169000000   75866139    4000000    2405146
Hyderabad                  172         39       1730       2387  122000000   60801747    9000000    5374314
Sukkur                     174         76       1640        985   75000000   37807433   11000000    3801934
Muzaffargarh                93         24       1760        791  113000000   42076712    7000000     492255
Multan                     232         65       4260       1719  224000000   95997303    9000000     741226
Sahiwal                     41         30       3170        520   77000000   13700652    2000000       9560
Bahawalpur                 169        128       1810       1906  109000000   50230405   18000000    2134213
Faisalabad Centeral        345         50       2510        680  141000000   46743255    6000000     451306
Faisalabad South           259         68       3430       6428  192000000   99824683   22000000     755685
Faisalabad North           285         57       5120      10545  287000000  137395705   21000000     717389
Sargodha                   131         36       1970        794   94000000   38358860    6000000     625323
Lahore South               405        101       5890      10000  365000000  207980162   20000000 2291699.43
Mangamandi                 207         55       9120       4111  512000000  212887714   15000000  1143193.8
Lahore Centeral            384        135       4480       4187  277000000  124372672   18000000    1411693
Shahdara                    95         48       2390        858  140000000   48475060    2000000     379638
Lahore North               538        130       6840       7031  421000000  214076589    7000000  1524612.8
Sheikhupura                 63         37       1590       2065  297000000  125485619   12000000    2852477
Gujranwala                 147         48       2600        650  177000000 72930245.7    6000000     675090
Gujrat                     120         15       1520        504   75000000   32232591    5000000     428525
Sialkot                    232        100       3080       2150  177000000 70255004.6    9000000    2075673
Jehlum                      90         38       1610       1324   80000000   33366358    4000000    2192451
Rawalpindi                 451         75       4980       5417  183000000  104970732    1000000    1104539
Islamabad                  201         80       7010       9888  356000000  188839282   12000000    5388175
Hasanabdal                 145         29       1660        348   58000000   23654085    2000000     304324
Peshawar                   299        108       3130       2951  136000000   66678676    4000000    1191715
Mardan                     157         43       2020       1514  100000000   41430837    4000000    2930836
Abbottabad                 180         52       2060       2149  101000000   43288123   22000000    1967729
Gilgit                      26         14        280        163   17000000    8484357    1000000      36834
Quetta                      78         15       1060        583   64000000   24009262    3000000     765418
Hub                         68         45       1190        828   89000000   45081727    3000000    2510129
                    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
total                     8400       2320     140000     138324 7984000000 3636536786  365000000  109709053

Regards
Michel

[Updated on: Mon, 01 February 2010 00:43]

Report message to a moderator

Re: Column total [message #441465 is a reply to message #441421] Mon, 01 February 2010 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And with ROLLUP:
SQL> select decode(grouping(name),1,'Total',NAME) name
  2         , sum(EMPLOYER_TARGET)            c1
  3         , sum(EMPLOYERS_REGISTERED)       c2
  4         , sum(EMPLOYEES_TARGET)           c3
  5         , sum(EMPLOYEES_REGISTERED)       c4
  6         , sum(CONTRIBUTION_TARGET)        c5
  7         , sum(CONTRIBUTION)               c6
  8         , sum(ARREAR_TARGET)              c7
  9         , sum(ARREARS)                    c8 /*
 10         , sum(TOTAL_CONTR)                c9
 11         , sum(PR02A_TARGET)               c10
 12         , sum(PR02A_ENTERED)              c11 */
 13  from RGN_ACHIVEMENTS
 14  group by rollup(name)
 15  /
NAME                        C1         C2         C3         C4         C5         C6         C7         C8
------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Abbottabad                 180         52       2060       2149  101000000   43288123   22000000    1967729
Bahawalpur                 169        128       1810       1906  109000000   50230405   18000000    2134213
Bin Qasim                  182         36       7170       3629  410000000  164230628    2000000    5382063
City                       436        106       6520      18125  407000000  212407495   13000000 2172351.14
Faisalabad Centeral        345         50       2510        680  141000000   46743255    6000000     451306
Faisalabad North           285         57       5120      10545  287000000  137395705   21000000     717389
Faisalabad South           259         68       3430       6428  192000000   99824683   22000000     755685
Gilgit                      26         14        280        163   17000000    8484357    1000000      36834
Gujranwala                 147         48       2600        650  177000000 72930245.7    6000000     675090
Gujrat                     120         15       1520        504   75000000   32232591    5000000     428525
Hasanabdal                 145         29       1660        348   58000000   23654085    2000000     304324
Hub                         68         45       1190        828   89000000   45081727    3000000    2510129
Hyderabad                  172         39       1730       2387  122000000   60801747    9000000    5374314
Islamabad                  201         80       7010       9888  356000000  188839282   12000000    5388175
Jehlum                      90         38       1610       1324   80000000   33366358    4000000    2192451
Karachi Central            330         38       4130       3622  236000000  123016640   10000000    4355696
Karimabad                  415        170       4650       4584  266000000  104412012   32000000   11346600
Korangi                    375         83       7440       6977  390000000  210851063   20000000   12563196
Kotri                      128         31       1840       2901  169000000   75866139    4000000    2405146
Lahore Centeral            384        135       4480       4187  277000000  124372672   18000000    1411693
Lahore North               538        130       6840       7031  421000000  214076589    7000000  1524612.8
Lahore South               405        101       5890      10000  365000000  207980162   20000000 2291699.43
Mangamandi                 207         55       9120       4111  512000000  212887714   15000000  1143193.8
Mardan                     157         43       2020       1514  100000000   41430837    4000000    2930836
Multan                     232         65       4260       1719  224000000   95997303    9000000     741226
Muzaffargarh                93         24       1760        791  113000000   42076712    7000000     492255
Nazimabad                  394         85       8410       1910  480000000  185588488   17000000   19142338
Peshawar                   299        108       3130       2951  136000000   66678676    4000000    1191715
Quetta                      78         15       1060        583   64000000   24009262    3000000     765418
Rawalpindi                 451         75       4980       5417  183000000  104970732    1000000    1104539
Sahiwal                     41         30       3170        520   77000000   13700652    2000000       9560
Sargodha                   131         36       1970        794   94000000   38358860    6000000     625323
Shahdara                    95         48       2390        858  140000000   48475060    2000000     379638
Sheikhupura                 63         37       1590       2065  297000000  125485619   12000000    2852477
Sialkot                    232        100       3080       2150  177000000 70255004.6    9000000    2075673
Sukkur                     174         76       1640        985   75000000   37807433   11000000    3801934
West Wharf                 353         30       9930      13100  567000000  248728469    6000000 6063705.64
Total                     8400       2320     140000     138324 7984000000 3636536786  365000000  109709053

38 rows selected.

Regards
Michel

[Updated on: Tue, 02 February 2010 01:24]

Report message to a moderator

Re: Column total [message #441591 is a reply to message #441465] Tue, 02 February 2010 01:20 Go to previous message
malhi
Messages: 96
Registered: December 2009
Location: Karachi
Member
Really Thankful....... for this precise solution
Previous Topic: Procedure back
Next Topic: BLOB
Goto Forum:
  


Current Time: Fri Dec 02 16:31:08 CST 2016

Total time taken to generate the page: 0.26917 seconds