Home » SQL & PL/SQL » SQL & PL/SQL » Spreadsheet-like Totals and Subtotals (11.2)
Spreadsheet-like Totals and Subtotals [message #674227] Fri, 11 January 2019 16:57 Go to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
I'm referencing this link https://stewashton.wordpress.com/2016/07/18/spreadsheet-like-totals-and-subtotals/

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/12-1/13) so I won't be able to reply at all...

Thanks, Larry

Re: Spreadsheet-like Totals and Subtotals [message #674234 is a reply to message #674227] Fri, 11 January 2019 19:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
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: Spreadsheet-like Totals and Subtotals [message #674253 is a reply to message #674227] Mon, 14 January 2019 10:42 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Wow, that is a much simpler Solomon, I greatly appreciate the solution

Thanks, Larry
Re: Spreadsheet-like Totals and Subtotals [message #674255 is a reply to message #674253] Mon, 14 January 2019 12:33 Go to previous messageGo to next message
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: Spreadsheet-like Totals and Subtotals [message #674256 is a reply to message #674255] Mon, 14 January 2019 15:32 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Solomon's code sorts by job, so naturally T comes before W. Why not just use the word "total" instead of "Total?"
Re: Spreadsheet-like Totals and Subtotals [message #674257 is a reply to message #674256] Mon, 14 January 2019 15:56 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
joy_division wrote on Mon, 14 January 2019 15:32
Solomon's code sorts by job, so naturally T comes before W. Why not just use the word "total" instead of "Total?"
Yes, I could but I suppose the sorting was done after the "select" thus causing the "order" not to display as I originally thought it would. I guess if I read the nice oracle manual it would have explained that to me.

Thanks for the reply Smile
Re: Spreadsheet-like Totals and Subtotals [message #674270 is a reply to message #674257] Tue, 15 January 2019 07:17 Go to previous messageGo to next message
Bill B
Messages: 1955
Registered: December 2004
Senior Member
Why not simply change the order by to

ORDER BY decode(job,'Total',2,1),job nulls last

That will always put the total line last
Re: Spreadsheet-like Totals and Subtotals [message #674275 is a reply to message #674255] Tue, 15 January 2019 09:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to pay more attention (btw, joy_division's assumption is incorrect too). Meditate over:

  order by data.job nulls last

vs:

  order by job nulls last

SY.

[Updated on: Tue, 15 January 2019 09:56]

Report message to a moderator

Re: Spreadsheet-like Totals and Subtotals [message #674277 is a reply to message #674275] Tue, 15 January 2019 13:00 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Solomon Yakobson wrote on Tue, 15 January 2019 10:49
You need to pay more attention (btw, joy_division's assumption is incorrect too). Meditate over:

  order by data.job nulls last

vs:

  order by job nulls last

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: Spreadsheet-like Totals and Subtotals [message #674278 is a reply to message #674275] Tue, 15 January 2019 13:27 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Solomon Yakobson wrote on Tue, 15 January 2019 09:49
You need to pay more attention (btw, joy_division's assumption is incorrect too). Meditate over:

  order by data.job nulls last

vs:

  order by job nulls last

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: Spreadsheet-like Totals and Subtotals [message #674279 is a reply to message #674278] Tue, 15 January 2019 15:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
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.
Re: Spreadsheet-like Totals and Subtotals [message #674280 is a reply to message #674279] Tue, 15 January 2019 15:30 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Solomon Yakobson wrote on Tue, 15 January 2019 15:06
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:
SY.
Thanks for the explanation Solomon. In my data model table, the column "job" is nullable but there are NO null data so it would seem either example would work for me.

Thanks again, Larry
Re: Spreadsheet-like Totals and Subtotals [message #674281 is a reply to message #674280] Tue, 15 January 2019 18:29 Go to previous message
Solomon Yakobson
Messages: 2953
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, it should be based on business rules and not on data currently in the table.

SY.
Previous Topic: Insert Statement taking more than 2 hrs for 4 million records
Next Topic: DBMS_REDACT with MERGE
Goto Forum:
  


Current Time: Wed Jun 03 06:13:57 CDT 2020