Count multiple Columns in Oracle SQL

Question

I am looking to count the occurrence of IDs in 3 different columns using SQL. The raw table would look like:

id | A  | B  | C
------------------
1  | A1 | B1 | C1
2  | A1 | B2 | C1
3  | A1 | B1 | C2
4  | A2 | B1 | C2
5  | A1 | B2 | C2

The desired Table should look like:

id | A  | count(A) | B  | count(B) | C  | count(C)
--------------------------------------------------
1  | A1 | 4        | B1 | 3        | C1 | 2
2  | A1 | 4        | B2 | 2        | C1 | 2
3  | A1 | 4        | B1 | 3        | C2 | 3
4  | A2 | 1        | B1 | 3        | C2 | 3
5  | A1 | 4        | B2 | 2        | C2 | 3

I tried the below query for a single column but didn't quite get the desired results:

SELECT A, COUNT(A) from table_name GROUP BY A;

But am unable to do the same for 3 columns.

Tim Biegeleisen · Accepted Answer · 2020-06-02 07:12:58Z

4

Use COUNT as analytic function:

SELECT
    id,
    A,
    COUNT(*) OVER (PARTITION BY A) cnt_A,
    B,
    COUNT(*) OVER (PARTITION BY B) cnt_B,
    C,
    COUNT(*) OVER (PARTITION BY C) cnt_C
FROM yourTable
ORDER BY id;

Demo

You don't want GROUP BY here, at least not by itself, because that aggregates the original records all of which you want to include in the output.

edited Jun 2, 2020 at 7:12

answered Jun 2, 2020 at 7:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count multiple Columns in Oracle SQL

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related