SQL COUNT all ROWS in Columns

Question

I need to Count all Rows and write it into Columns in ORACLE SQL. It schould look like that (left as it is, right as it should look like):

|A|B|C|D|      |A|COUNT_A|B|COUNT_B|C|COUNT_C|D|COUNT_D|
 - - - -        - ------- - ------- - ------- - -------
|1|2|3| |  ==> |1|   2   |2|   3   |3|   3   | |   0   |
|1|3|4| |  ==> |1|   2   |3|   3   |4|   3   | |   0   |
| |3|4| |      | |   2   |3|   3   |4|   3   | |   0   |

I've tried serveral things like COUNT(*) OVER (PARTITION BY), UNION an so on

Thank you in advance.

score 1 · Answer 1 · answered Jan 21 '21 at 18:30

1

You can use window functions:

select a, count(a) over () as cnt_a,
       b, count(b) over () as cnt_b,
       c, count(c) over () as cnt_c,
       d, count(d) over () as cnt_d
from t;

answered Jan 21 '21 at 18:30

Gordon Linoff

1,242,037
58
646
786

Thanks a lot. Didn't know that a OVER without PARTITION BY Expression ist possible – Pohly91 Jan 21 '21 at 19:15
@Pohly91 . . . In that case, it references the entire result set. – Gordon Linoff Jan 22 '21 at 00:38

score 0 · Answer 2 · answered Jan 21 '21 at 18:36

You can do it with an aggregate query and a cross join. If you have a significant amount of data, it will be interesting to compare this to the analytic function approach shown in Gordon Linoff's answer; the aggregate approach reads the data from the base table twice (which will take more time), but aggregate functions are computed much faster than analytic functions, even when they do the same thing.

Something like this:

select a, cnt_a, b, cnt_b, c, cnt_c, d, cnt_d
from   t cross join
       ( select count(a) cnt_a, count(b) cnt_b, count(c) cnt_c, count(d) cnt_d
         from   t
       )
;

SQL COUNT all ROWS in Columns

2 Answers2