How do you display one query result into 2 rows?

Question

So this is base query of something. It displays total # of each columns and % of them in one row but I want it to display in 2 rows. 1st row displays only #s while 2nd row displays only percentage of each column in first row.

select column1,
       column2,
       column3,
       total,
       round((column1 / total) * 100, 2) as column1_percent,
       round((column2 / total) * 100, 2) as column2_percent,
       round((column3 / total) * 100, 2) as column3_percent,
       round((total / total) * 100, 2) as total_percent,
  from (select column1,
               column2,
               column3,
               (column1 + column2 + column3) as total
          from (select (select count(x) from table1 a, table2 b where ~~) as column1,
                       (select count(x) from table3 a, table4 b where ~~) as column2,
                       (select count(x) from table5 a, table6 b where ~~) as column3
                  from dual));

How do I make this work? Help.

Why are you choosing not to use proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Aug 14 '20 at 14:27
Does this answer your question? [How to select multiple rows filled with constants?](https://stackoverflow.com/questions/2201170/how-to-select-multiple-rows-filled-with-constants) — SaintLike, Aug 14 '20 at 14:30
What Gordon is talking about is that comma-separated joins, e.g. `from table1 a, table2 b ...` were used before explicit joins (e.g. `from table1 a inner join table2 b on ...`) got introduced in the SQL standard. That was in **1992**. (It took Oracle some years to adopt it, though.) So, either you are dealing with a very old query here or you may have been taught SQL by a very bad book, tutorial or teacher. — Thorsten Kettner, Aug 14 '20 at 14:59

Barbaros Özhan · Accepted Answer · 2020-08-14T14:45:10.020

You can use Common Table Expression along with UNION ALL

with cte as
(
select column1, column2, column3, (column1 + column2 + column3) as total
from (
  select
    (select count(x) from table1 a cross join table2 b where ~~) as column1,
    (select count(x) from table3 a cross join table4 b where ~~) as column2,
    (select count(x) from table5 a cross join table6 b where ~~) as column3
  from dual
)
)
select column1, column2, column3, total 
  from cte
 union all
select round((column1/total) *100, 2), 
       round((column2/total) *100, 2), 
       round((column3/total) *100, 2), 
       round((total/total) *100, 2) 
  from cte

where aliasing is found out within the topmost one among the queries those are linked by UNION ALL

Worked like charm. Thank you. – user11666603 Aug 14 '20 at 14:43 — user11666603, Aug 14 '20 at 14:43

How do you display one query result into 2 rows?

1 Answers1