sql - SQL查询以获取子计数并将其求和

Question

我的桌子有点像：

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

我想要得到的结果是：

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

所以SUB_CODE_COUNT是每个唯一的总和SUB_CODE，并且CODE_COUNT是所有的SUB_CODE总和CODE

这是我现在拥有的：

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

结果：

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

标签： sqloracle

Answer 1

使用分析函数：

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;