如何对Oracle中的重复行进行计数，然后根据重复行数进行计算

Question

例如，我有一个这样的表：

Name            Grade
John Smith      3
John Smith      3
John Smith      3

所以基本上我想要计算重复的行数，然后用这个数字乘以成绩，得到一个总成绩。

最后，它应该看起来像这样：

Name            Grade
John Smith      9

Answer 1

如果您只想列出重复项，那么：

SELECT name,
       SUM( grade ) AS total_grade
FROM   table_name
GROUP BY name
HAVING   COUNT(1) > 1

如果希望包含唯一行，则省略HAVING子句。

如果同一名称可以有不同的等级，并且您只想考虑名称和等级的重复，则：

CREATE TABLE table_name ( name, grade ) AS
SELECT 'John Smith', 3 FROM DUAL UNION ALL
SELECT 'John Smith', 3 FROM DUAL UNION ALL
SELECT 'John Smith', 3 FROM DUAL UNION ALL
SELECT 'John Smith', 2 FROM DUAL UNION ALL
SELECT 'John Smith', 2 FROM DUAL UNION ALL
SELECT 'John Smith', 1 FROM DUAL;

SELECT name,
       grade,
       COUNT(1) * grade AS total_grade
FROM   table_name
GROUP BY name, grade
HAVING   COUNT(1) > 1;

将输出：

NAME            GRADE TOTAL_GRADE
---------- ---------- -----------
John Smith          2           4 
John Smith          3           9 

Answer 2

这称为group by

select name, sum(grade)
from t
group by name;