CODEWITHSUNDEEP
sqloracle-database
Rajat
Rajat

Reputation: 35

Count multiple Columns in Oracle SQL

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.

Upvotes: 0

Views: 904

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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;

screen capture from demo below

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.

Upvotes: 4

Related Questions