CODEWITHSUNDEEP
sqloracle-database
Shank
Shank

Reputation: 1398

SQL query to get child count and sum it

My table is kind of like :

    ID    CODE    SUB_CODE
  ------ ------ ------------
  |  1  |  A   |    A1     |
  --------------------------
  |  2  |  A   |    A1     |
  --------------------------
  |  3  |  A   |    A3     |
  --------------------------
  |  4  |  B   |    B1     |

the result I am trying to get is : 

CODE   CODE_COUNT   SUB_CODE   SUB_CODE_COUNT
 A         3           A1            2
 A         3           A3            1
 B         1           B1            1

So the SUB_CODE_COUNT is the sum count of each unique SUB_CODE, and CODE_COUNT is the sum count of all SUB_CODE for that CODE

This what I have right now:

SELECT
   CODE,
   SUB_CODE,
   COUNT(SUB_CODE) AS SUB_CODE_COUNT,
FROM
   TABLENAME
GROUP BY
   CODE, SUB_CODE

Result:

CODE   SUB_CODE   SUB_CODE_COUNT
 A        A1            2
 A        A3            1
 B        B1            1

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Use analytic functions:

SELECT CODE, SUB_CODE,
       COUNT(*) AS SUB_CODE_COUNT,
       SUM(COUNT(*)) OVER (PARTITION BY CODE) as CODE_COUNT
FROM TABLENAME
GROUP BY CODE, SUB_CODE;

Upvotes: 2

Related Questions