Home » SQL & PL/SQL » SQL & PL/SQL » SELECT statement with subtotal and total & Rollup
SELECT statement with subtotal and total & Rollup [message #208500] Mon, 11 December 2006 01:42 Go to next message
anbazsg
Messages: 8
Registered: December 2006
Junior Member
Hi all,

i want to format my data

my actual data is the below

-----------------------------------------------------------
proc_num Family Error_type PartNum Errpr_msg
-----------------------------------------------------------
1 9AX752 ERROR3 3PN01HPX invalid number

1 9AX752 ERROR3 3PN02Vb7 invalid HdaNum

1 9AX752 ERROR3 3WN01HgX invalid Bupmap

1 9BD148 ERROR7 3QD0014V HDA notfound

1 9BD148 ERROR7 3QV0044D Resacn again


but i want to display the data like the below format


-----------------------------------------------------------
proc_num Family Error_type PartNum Errpr_msg
-----------------------------------------------------------

1 9AX752 ERROR3 3PN01HPX invalid number
3PN02Vb7 invalid HdaNum
3WN01HgX invalid Bupmap
-------------
SubTotal 3

1 9BD148 ERROR7 3QD0014V HDA notfound
3QV0044D Resacn again
-------------
Sub Total 2
Grand Total 5
so any one pls help me to write the sql statment with sub total and grand total with rollup ..

i want the select query with proc_num column group and count(partNum) with rollup for subtotal and grand total..

i attached my txt file for data format.

with regsrds,
anbaz.


  • Attachment: sample.txt
    (Size: 1.13KB, Downloaded 153 times)

[Updated on: Mon, 11 December 2006 20:23]

Report message to a moderator

Re: SELECT stament with subtotal and total & Rollup [message #208590 is a reply to message #208500] Mon, 11 December 2006 08:06 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
This is the closest I've been able to get to your requirement:

drop table rollup_test;

create table rollup_test (proc_num varchar2(5), Family varchar2(10), Error_type varchar2(10), PartNum varchar2(10), Error_msg varchar2(50));

insert into rollup_test values
('1','9AX752', 'ERROR3', '3PN01HPX', 'invalid number');
 
insert into rollup_test values
('1','9AX752', 'ERROR3', '3PN02Vb7', 'invalid HdaNum');

insert into rollup_test values
('1','9AX752', 'ERROR3', '3WN01HgX', 'invalid Bupmap');

insert into rollup_test values
('1','9BD148', 'ERROR7', '3QD0014V', 'HDA notfound ');

insert into rollup_test values
('1','9BD148', 'ERROR7', '3QV0044D', 'Resacn again');

select case when grp_proc_num = 1 and grp_err_type = 1 then 'Grand Total' 
            when grp_proc_num = 0 and grp_err_type = 1 then 'Proc Subtotal for '||proc_num 
            when grp_proc_num = 0 and grp_err_type = 0 and grp_family = 1 then 'Err Type Subtotal for '||error_type
            else null end  as record_type, 
       case when grp_family = 1 then null else proc_num end as proc_num, 
       case when grp_family = 1 then null else error_type end as error_type,
       Family, PartNum, Error_msg, rec_count 
from
(select grouping(proc_num) as grp_proc_num, 
        grouping(error_type) as grp_err_type,
        grouping(family) as grp_family,
        proc_num,
        error_type,
        family,
        partnum,
        error_msg,
        count(*) as rec_count		   
from rollup_test
group by grouping sets ((proc_num, error_type, Family, Error_type, PartNum, Error_msg)
                        ,rollup(proc_num, error_type))		
order by proc_num nulls last, error_type)


Produces:

RECORD_TYPE                      PROC_NUM ERROR_TYPE FAMILY     PARTNUM    ERROR_MSG                                          REC_COUNT                              
                                 1        ERROR3     9AX752     3PN01HPX   invalid number                                     1                                      
                                 1        ERROR3     9AX752     3PN02Vb7   invalid HdaNum                                     1                                      
                                 1        ERROR3     9AX752     3WN01HgX   invalid Bupmap                                     1                                      
Err Type Subtotal for ERROR3                                                                                                  3                                      
                                 1        ERROR7     9BD148     3QD0014V   HDA notfound                                       1                                      
                                 1        ERROR7     9BD148     3QV0044D   Resacn again                                       1                                      
Err Type Subtotal for ERROR7                                                                                                  2                                      
Proc Subtotal for 1                                                                                                           5                                      
Grand Total                                                                                                                   5                                      
Re: SELECT statement with subtotal and total & Rollup [message #208663 is a reply to message #208590] Mon, 11 December 2006 19:41 Go to previous messageGo to next message
anbazsg
Messages: 8
Registered: December 2006
Junior Member
Hi Cthulhu,

fine, but that is not my requirement, little bit different,

actually my table name is: amts_log

my cloumns name's are proc_num,family,error_type,part_num,error_msg. so totally 7 columns from the amts_log table.

my actual task is i need to group the family column with error_type column then need to count the part_num column , place the sub total for each group of family and finally grand total. this is my requirement.

my acutal data format need to display , i attached in the txt file.

pls look the attachment and give me a suitable solution.

thanks & regards,
anbaz.




  • Attachment: sample.txt
    (Size: 1.43KB, Downloaded 131 times)

[Updated on: Mon, 11 December 2006 20:22]

Report message to a moderator

Re: SELECT statement with subtotal and total & Rollup [message #208767 is a reply to message #208663] Tue, 12 December 2006 03:12 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I'm sure there is much to be achieved by posting the same question again, as you appear to have just done.

To get it in exactly the format you want, you are unlikely to be able to do this with straight SQL. You will need to use a reporting tool of some kind or possibly SQL*Plus with the break/compute commands.
Re: SELECT statement with subtotal and total & Rollup [message #208770 is a reply to message #208500] Tue, 12 December 2006 03:22 Go to previous messageGo to next message
anbazsg
Messages: 8
Registered: December 2006
Junior Member
Hi Cthulhu,

i am new one to SQL side, so can i have clear SQL statments for my actual format.

atleast like the below format:

-----------------------------------------------------------
Family Error_type PartNum proc_num Error_msg
-----------------------------------------------------------
9AX752 ERROR3 3PN01HPX 1 invalid number

9AX752 ERROR3 3PN02Vb7 1 invalid HdaNum

9AX752 ERROR3 3WN01HgX 1 invalid Bupmap
----------
Sub total 3


9BD148 ERROR7 3QD0014V 1 HDA notfound

9BD148 ERROR7 3QV0044D 1 Resacn again
----------
Sub total 2


9XV952 ERROR9 3QD0014V 1 invalid bupnum

9XV952 ERROR9 3QV0044D 1 inalid serialnum

9xv952 ERROR9 3NPV004S 1 invalid bupmap
----------
Sub total 3


grand total 8


then i will be very happy .

with regards,
anbaz.
Re: SELECT statement with subtotal and total & Rollup [message #208773 is a reply to message #208500] Tue, 12 December 2006 03:48 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
There are no SQL statements for your actual format, since SQL produces tabular results and you are producing a report with headers and footers. In SQL*Plus, you can use:

set linesize 132
break on proc_num skip 2 on error_type skip 1
compute count label 'Grand Total' of error_msg on proc_num
compute count label 'Sub Total' of error_msg on error_type

select * from rollup_test
/

That's really the best I can suggest without using a proper reporting tool.
Previous Topic: Overlapping intervals
Next Topic: Check for view?
Goto Forum:
  


Current Time: Wed Dec 07 22:36:57 CST 2016

Total time taken to generate the page: 0.08118 seconds