CODEWITHSUNDEEP
sqloracle-databaseoracle11g
D0nK3Y_D0nK
D0nK3Y_D0nK

Reputation: 63

How to count duplicated rows and then do a calculation based on the amount of duplicates in Oracle

For example I have a table like this:

Name            Grade
John Smith      3
John Smith      3
John Smith      3

So basically I want to count the duplicate row and to multiply that number by the grade to get a total grade.

In final it should looks like this:

Name            Grade
John Smith      9

Upvotes: 0

Views: 42

Answers (2)

MT0
MT0

Reputation: 167972

If you only want to list duplicates then:

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

If you want to include unique rows then leave out the HAVING clause.

If there can be different grades for the same name and you only want to consider duplicates of both name and grade then:

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;

Would output:

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is called group by:

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

Upvotes: 1

Related Questions