Home » SQL & PL/SQL » SQL & PL/SQL » Spreadsheetlike Totals and Subtotals (11.2)
Spreadsheetlike Totals and Subtotals [message #674227] 
Fri, 11 January 2019 16:57 

lott42_gmail
Messages: 142 Registered: June 2010

Senior Member 


I'm referencing this link https://stewashton.wordpress.com/2016/07/18/spreadsheetliketotalsandsubtotals/
in trying to display data counts in "spreadsheet" type format.
By referencing code in the link, I came up with something like this
with data as(
select '1' job, 10 deptno from dual union all
select '1' job, 20 deptno from dual union all
select '2' job, 10 deptno from dual union all
select '3' job, 10 deptno from dual union all
select '3' job, 20 deptno from dual union all
select '3' job, 30 deptno from dual union all
select '4' job, 20 deptno from dual union all
select '5' job, 10 deptno from dual union all
select '5' job, 30 deptno from dual
)
select case gr_job when 1 then 'Total' else job end job,
"10", "20", "30", "40", "Total"
from (
select case grouping(deptno) when 1 then 1 else deptno end deptno,
job, grouping(job) gr_job, count(*) sal
from data
group by cube(deptno, job)
)
pivot(
max(sal) for deptno in (10, 20, 30, 40, 1 as "Total")
)
order by gr_job, job;
and results like
ID 10 20 30 TOTAL
1 1 1 2
2 1 1
3 1 1 1 3
4 1 1
5 1 1 2
Total 4 3 2 9
BUT, if a change the "Dept" from a number to a char
with data as(
select '1' job, 'A' deptno from dual union all
select '1' job, 'B' deptno from dual union all
select '2' job, 'A' deptno from dual union all
select '3' job, 'A' deptno from dual union all
select '3' job, 'B' deptno from dual union all
select '3' job, 'C' deptno from dual union all
select '4' job, 'B' deptno from dual union all
select '5' job, 'A' deptno from dual union all
select '5' job, 'C' deptno from dual
)
select case gr_job when 1 then 'Total' else job end job,
'A', 'B', 'C', 'Total'
from (
select case grouping(deptno) when 1 then '1' else deptno end deptno,
job, grouping(job) gr_job, count(*) sal
from data
group by cube(deptno, job)
)
pivot(
max(sal) for deptno in ('A', 'B', 'C', 1 as "Total")
)
order by gr_job, job;
I get results like
ID 10 20 30 TOTAL
1 A B C Total
2 A B C Total
3 A B C Total
4 A B C Total
5 A B C Total
Total A B C Total
Why does changing the "Dept" to a char change my results?? I'll change "sal" to something more meaningful later...
I'll be gone over the weekend(1/121/13) so I won't be able to reply at all...
Thanks, Larry



Re: Spreadsheetlike Totals and Subtotals [message #674234 is a reply to message #674227] 
Fri, 11 January 2019 19:11 
Solomon Yakobson
Messages: 2966 Registered: January 2010 Location: Connecticut, USA

Senior Member 


You greatly overcomplicated it:
with data as(
select '1' job, 'A' deptno from dual union all
select '1' job, 'B' deptno from dual union all
select '2' job, 'A' deptno from dual union all
select '3' job, 'A' deptno from dual union all
select '3' job, 'B' deptno from dual union all
select '3' job, 'C' deptno from dual union all
select '4' job, 'B' deptno from dual union all
select '5' job, 'A' deptno from dual union all
select '5' job, 'C' deptno from dual
)
select nvl(job,'Total') job,
sum(
case deptno
when 'A' then 1
end
) a,
sum(
case deptno
when 'B' then 1
end
) b,
sum(
case deptno
when 'C' then 1
end
) c,
count(*) total
from data
group by rollup(job)
order by data.job nulls last
/
JOB A B C TOTAL
    
1 1 1 2
2 1 1
3 1 1 1 3
4 1 1
5 1 1 2
Total 4 3 2 9
6 rows selected.
SQL>
SY.




Re: Spreadsheetlike Totals and Subtotals [message #674255 is a reply to message #674253] 
Mon, 14 January 2019 12:33 

lott42_gmail
Messages: 142 Registered: June 2010

Senior Member 


Solomon, I'm not sure if that solution is correct either.
I changed the data/code to this
with data as
(
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'MK4' job, 'M' deptno from dual union all
select 'WAT' job, 'M' deptno from duaL
)
select nvl(job,'Total') job,
sum(
case deptno
when 'T' then 1
end
) t,
sum(
case deptno
when 'R' then 1
end
) r,
sum(
case deptno
when 'M' then 1
end
) m,
count(*) total
from data
group by rollup(job)
order by job nulls last;
with these results
AN 2 2
MK4 3 2 1 6
Total 3 2 4 9
WAT 1 1
The "Total" line is sorted before the "WAT" line. I assume the "Order by nulls last" is occurring after the select clause
If I change it to
with data as
(
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'MK4' job, 'M' deptno from dual union all
select 'WAT' job, 'M' deptno from duaL
)
SELECT
nvl(job,'Total') job,
m,
r,
t,
total
FROM
(
SELECT
job,
SUM(
CASE deptno
WHEN 'T' THEN 1
END
) t,
SUM(
CASE deptno
WHEN 'R' THEN 1
END
) r,
SUM(
CASE deptno
WHEN 'M' THEN 1
END
) m,
COUNT(*) total
FROM
data
GROUP BY
ROLLUP(job)
ORDER BY
job nulls last
) t;
I get these results
AN 2 2
MK4 1 2 3 6
WAT 1 1
Total 4 2 3 9
Am I on the correct assumption on how the data is processed??
Thanks, Larry







Re: Spreadsheetlike Totals and Subtotals [message #674277 is a reply to message #674275] 
Tue, 15 January 2019 13:00 
joy_division
Messages: 4949 Registered: February 2005 Location: East Coast USA

Senior Member 


Solomon Yakobson wrote on Tue, 15 January 2019 10:49You need to pay more attention (btw, joy_division's assumption is incorrect too). Meditate over:
order by data.job nulls last
vs:
SY.
Ah yes, my haste in glancing over the question (after the original data changed) caused me to make a dumb mistake. Thanks Solomon.



Re: Spreadsheetlike Totals and Subtotals [message #674278 is a reply to message #674275] 
Tue, 15 January 2019 13:27 

lott42_gmail
Messages: 142 Registered: June 2010

Senior Member 


Solomon Yakobson wrote on Tue, 15 January 2019 09:49You need to pay more attention (btw, joy_division's assumption is incorrect too). Meditate over:
order by data.job nulls last
vs:
SY.
Yes, I see the difference although I'm not exactly sure why referring back to "data" makes a difference.
with data as
(
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'MK4' job, 'M' deptno from dual union all
select 'WAT' job, 'M' deptno from duaL
)
select nvl(job,'Total') job,
sum(
case deptno
when 'T' then 1
end
) t,
sum(
case deptno
when 'R' then 1
end
) r,
sum(
case deptno
when 'M' then 1
end
) m,
count(*) total
from data
group by rollup(job)
order by job nulls last;
order by data.job nulls last;
order by decode(job,'Total',2,1),job nulls last



Re: Spreadsheetlike Totals and Subtotals [message #674279 is a reply to message #674278] 
Tue, 15 January 2019 15:06 
Solomon Yakobson
Messages: 2966 Registered: January 2010 Location: Connecticut, USA

Senior Member 


ORDER BY allows referencing both table columns and select list aliases. If column name and alias name are the same alias takes precedence. So ORDER BY job nulls last is actually sorting by alias job which is nvl(job,'Total') and since nvl(job,'Total') value for rollup row is 'Total' rollup row appears before WAT. And when we use ORDER BY data.job nulls last we are sorting by table data column job. And since column job value for rollup row is NULL and we say nulls last, rollup row (well, unless null column job is possible) appears last. I assumed column job is not null. If job can be null, then we should rely on grouping(data.job) which returns 1 for rollup row and 0 for all other rows:
select case grouping(data.job) when 1 then 'Total' else job end job
And:
order by grouping(data.job),
job
with data as
(
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'T' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'MK4' job, 'R' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'AN' job, 'M' deptno from dual union all
select 'MK4' job, 'M' deptno from dual union all
select 'WAT' job, 'M' deptno from duaL
)
select case grouping(data.job) when 1 then 'Total' else job end job,
sum(
case deptno
when 'T' then 1
end
) t,
sum(
case deptno
when 'R' then 1
end
) r,
sum(
case deptno
when 'M' then 1
end
) m,
count(*) total
from data
group by rollup(job)
order by grouping(data.job),job nulls last
/
JOB T R M TOTAL
    
AN 2 2
MK4 3 2 1 6
WAT 1 1
Total 3 2 4 9
SQL>
SY.





Goto Forum:
Current Time: Wed Jul 08 17:33:49 CDT 2020
