I am trying to add the subtotal of loans and total numbers of weeks by the program and add a grand total for all loans and all weeks at the last row.
I've tried grouping sets and rollup, but the result is unchanged or wrong...
Here is the table:
STUDENT (**St_ID**, St_LName, St_FName, Email, Prog_ID@)
LOCATION **(Location_ID**, Loc_Bldg, Loc_Room)
ITEM (**Item_ID**, Item_Manuf, Item_Model, Comments1)
COMPUTER (**Comp_ID**, Comp_Name, Year, Cost, Location_ID@, Item_ID@, Vendor_ID@)
LOAN (**Loan_ID**, St_ID@, Comp_ID@, Start_Date, Date_Returned)
PROGRAM (**Prog_ID**, Name)
VENDOR (**Vendor_ID,** Name, Contact_FName, Contact_LName, Phone, Email)
My query and output, but I don't know how to add the subtotal and grand total...
select program.Name Prog_Name, student.st_Lname||', '||st_Fname st_name, loan_id, loc_bldg||', '||loc_room location,
to_char((date_returned-start_date)/7, '99') weeks
from program right join student using (prog_id)
left join loan using (st_id)
join computer using (comp_id)
join location using (location_id)
group by grouping sets((program.Name,st_Lname||', '||st_Fname,loan_id, loc_bldg||', '||loc_room,
(date_returned-start_date)))
order by 1,2;
PROG_NAME ST_NAME LOAN_ LOCATION WEEKS
------------------------------ -------------------- ----- --------------------------- ----------
Information System Jiang, Yaohan 0010 Cyert Hall, 0701 0
Jiang, Yaohan 0012 Cyert Hall, 0701 2
Jiang, Yaohan 0013 Cyert Hall, 0701 6
Jiang, Yaohan 0014 Tepper Quad, 1009 7
Jiang, Yaohan 0016 Warner Hall, 1304 7
Xiao, Shan 0007 Cyert Hall, 0701 9
Xu, Sheng 0001 Baker Building, 1101 11
Xu, Sheng 0006 Porter Hall, 1004 9
Information Technology Ouyang, Hsuan 0004 Baker Building, 1101 1
Ouyang, Hsuan 0008 Tepper Quad, 1009 5
Peng, Bo 0003 Warner Hall, 1304 1
Peng, Bo 0015 Warner Hall, 1304
Wu, Shinyu 0002 Tepper Quad, 1009 4
Wu, Shinyu 0005 Tepper Quad, 1009 0
Yin, Abby 0009 Tepper Quad, 1009 1
The issue is a) your grouping sets aren't correct, and b) you don't have any aggregate functions to do the grouping sets against.
I think the following is what you're after:
WITH your_results AS (SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 10 loan, 'Cyert Hall, 0701' LOCATION, 0 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 12 loan, 'Cyert Hall, 0701' LOCATION, 2 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 13 loan, 'Cyert Hall, 0701' LOCATION, 6 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 14 loan, 'Tepper Quad, 1009' LOCATION, 7 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Jiang, Yaohan' st_name, 16 loan, 'Warner Hall, 1304' LOCATION, 7 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Xiao, Shan' st_name, 7 loan, 'Cyert Hall, 0701' LOCATION, 9 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Xu, Sheng' st_name, 1 loan, 'Baker Building, 1101' LOCATION, 11 weeks FROM dual UNION ALL
SELECT 'Information System' prog_name, 'Xu, Sheng' st_name, 6 loan, 'Porter Hall, 1004' LOCATION, 9 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Ouyang, Hsuan' st_name, 4 loan, 'Baker Building, 1101' LOCATION, 1 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Ouyang, Hsuan' st_name, 8 loan, 'Tepper Quad' LOCATION, 5 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Peng, Bo' st_name, 3 loan, 'Warner Hall, 1304' LOCATION, 1 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Peng, Bo' st_name, 15 loan, 'Warner Hall, 1304' LOCATION, NULL weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Wu, Shinyu' st_name, 2 loan, 'Tepper Quad' LOCATION, 4 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Wu, Shinyu' st_name, 5 loan, 'Tepper Quad' LOCATION, 0 weeks FROM dual UNION ALL
SELECT 'Information Technology' prog_name, 'Yin, Abby' st_name, 9 loan, 'Tepper Quad' LOCATION, 1 weeks FROM dual)
SELECT prog_name,
st_name,
sum(loan) loan,
LOCATION,
sum(weeks) weeks
FROM your_results
GROUP BY GROUPING SETS ((prog_name, st_name, loan, location, weeks), (prog_name), ())
ORDER BY prog_name, st_name, weeks;
PROG_NAME ST_NAME LOAN LOCATION WEEKS
---------------------- ------------- ---------- -------------------- ----------
Information System Jiang, Yaohan 10 Cyert Hall, 0701 0
Information System Jiang, Yaohan 12 Cyert Hall, 0701 2
Information System Jiang, Yaohan 13 Cyert Hall, 0701 6
Information System Jiang, Yaohan 14 Tepper Quad, 1009 7
Information System Jiang, Yaohan 16 Warner Hall, 1304 7
Information System Xiao, Shan 7 Cyert Hall, 0701 9
Information System Xu, Sheng 6 Porter Hall, 1004 9
Information System Xu, Sheng 1 Baker Building, 1101 11
Information System 79 51
Information Technology Ouyang, Hsuan 4 Baker Building, 1101 1
Information Technology Ouyang, Hsuan 8 Tepper Quad, 1009 5
Information Technology Peng, Bo 3 Warner Hall, 1304 1
Information Technology Peng, Bo 15 Warner Hall, 1304
Information Technology Wu, Shinyu 5 Tepper Quad, 1009 0
Information Technology Wu, Shinyu 2 Tepper Quad, 1009 4
Information Technology Yin, Abby 9 Tepper Quad, 1009 1
Information Technology 46 12
125 63
(You would replace the your_results subquery with the query that returns the data you're trying to group against.)
This has the advantage of not requiring SQL*Plus features (break, compute).
If you still only want to output the prog_name for the first row without using SQL*Plus features, you would do:
SELECT CASE WHEN rn = 1 THEN pn END prog_name,
st_name,
loan,
LOCATION,
weeks
FROM (SELECT prog_name pn,
st_name,
sum(loan) loan,
LOCATION,
sum(weeks) weeks,
row_number() OVER (PARTITION BY prog_name ORDER BY st_name, weeks, LOCATION) rn
FROM your_results
GROUP BY GROUPING SETS ((prog_name, st_name, loan, location, weeks), (prog_name), ()))
ORDER BY pn, st_name, weeks, LOCATION;