OraFAQ Forum: SQL & PL/SQL » help code required

Home » SQL & PL/SQL » SQL & PL/SQL » help code required (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help code required [message #338318]

Mon, 04 August 2008 10:05

andi_raj
Messages: 55
Registered: February 2008

Member

Quote:

Table name : sales
tdate date
tamt number(10,2)

existing data are as follows:

tdate tamt
------- ------
01/01/2008 100.00
01/01/2008 200.00
02/01/2008 300.00
01/01/2008 400.00
03/01/2008 300.00
01/01/2008 150.00
02/01/2008 200.00
02/01/2008 150.00
03/01/2008 200.00

I want to have the following report using single select
statement:

sno tdate tamt
----- ------- ---------
1 01/01/2008 100.00
2 01/01/2008 200.00
3 01/01/2008 400.00
4 01/01/2008 150.00
---------
850.00
---------
1 02/01/2008 300.00
2 02/01/2008 200.00
3 02/01/2008 150.00
---------
650.00
---------
1 03/01/2008 300.00
2 03/01/2008 200.00
---------
500.00
---------
Grand total 2000.00
---------

i tried various options i encountered the following problems:
1) Serial number is not getting break for each day break
2) sub total and grand total is not coming.
my boss says it is possible to achieve through single select statement but he could not remember it. can any please help me?
thanks in advance
raj

Report message to a moderator

Re: help code required [message #338325 is a reply to message #338318]

Mon, 04 August 2008 10:37

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Your boss is right it is possible.

Regards
Michel

Report message to a moderator

Re: help code required [message #338406 is a reply to message #338318]

Mon, 04 August 2008 19:41

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

There are different ways that you can do something similar. You can use an analytic function like row_number to assign an sno. You can then either use that in a subquery and rollup to get the totals in an outer query or you can use some SQL*Plus commands to get the totals. Please see the demonstration below.

-- test data:

SCOTT@orcl_11g> create table sales as
  2  select decode (deptno,
  3  		    10, to_date ('01/01/2008', 'dd/mm/yyyy'),
  4  		    20, to_date ('02/01/2008', 'dd/mm/yyyy'),
  5  		    30, to_date ('03/01/2008', 'dd/mm/yyyy'))
  6  	      as tdate,
  7  	    sal      as tamt
  8  from   emp
  9  /

Table created.

SCOTT@orcl_11g> select tdate, tamt
  2  from   sales
  3  order  by tdate
  4  /

TDATE           TAMT
--------- ----------
01-JAN-08       2450
01-JAN-08       5000
01-JAN-08       1300
02-JAN-08       2975
02-JAN-08       3000
02-JAN-08       1100
02-JAN-08        800
02-JAN-08       3000
03-JAN-08       1250
03-JAN-08       1500
03-JAN-08       1600
03-JAN-08        950
03-JAN-08       2850
03-JAN-08       1250

14 rows selected.

-- sql only:

SCOTT@orcl_11g> select sno, tdate, sum (tamt) as tamt
  2  from   (select row_number () over (partition by tdate order by rowid) as sno,
  3  		    tdate, tamt
  4  	     from   sales)
  5  group  by rollup (tdate, sno)
  6  order  by tdate, sno
  7  /

       SNO TDATE           TAMT
---------- --------- ----------
         1 01-JAN-08       2450
         2 01-JAN-08       5000
         3 01-JAN-08       1300
           01-JAN-08       8750
         1 02-JAN-08        800
         2 02-JAN-08       2975
         3 02-JAN-08       3000
         4 02-JAN-08       1100
         5 02-JAN-08       3000
           02-JAN-08      10875
         1 03-JAN-08       1600
         2 03-JAN-08       1250
         3 03-JAN-08       1250
         4 03-JAN-08       2850
         5 03-JAN-08       1500
         6 03-JAN-08        950
           03-JAN-08       9400
                          29025

18 rows selected.

-- sql*plus and sql:

SCOTT@orcl_11g> break	on tdate skip 1 on report
SCOTT@orcl_11g> compute sum label '' of tamt on tdate
SCOTT@orcl_11g> compute sum label 'Grand total' of tamt on report
SCOTT@orcl_11g> column	sno format 99999999999
SCOTT@orcl_11g> select	row_number () over (partition by tdate order by rowid) as sno,
  2  	     tdate, tamt
  3  from    sales
  4  order   by tdate, sno
  5  /

         SNO TDATE           TAMT
------------ --------- ----------
           1 01-JAN-08       2450
           2                 5000
           3                 1300
             ********* ----------
                             8750

           1 02-JAN-08        800
           2                 2975
           3                 3000
           4                 1100
           5                 3000
             ********* ----------
                            10875

           1 03-JAN-08       1600
           2                 1250
           3                 1250
           4                 2850
           5                 1500
           6                  950
             ********* ----------
                             9400

                       ----------
Grand total                 29025

14 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: help code required [message #338415 is a reply to message #338406]

Mon, 04 August 2008 23:24

andi_raj
Messages: 55
Registered: February 2008

Member

To Michael,
I will follow the rules.
To Barbara
Thank you very much for the timely help.
raj

Report message to a moderator

Previous Topic:	Loop Through String Variable
Next Topic:	Cursor Already Open

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 18 10:56:41 CST 2025