CODEWITHSUNDEEP
sqldatabaseoracle-databasesum
Ahmed Emad
Ahmed Emad

Reputation: 560

Oracle : SUM Column of only duplicates value of other column

I have Table as below : 

Name     |Number
------------------
name1    |15 
name1    |7
name2    |10
name3    |9
name4    |11
name4    |4

I want to return total count of(Number) for only duplicates (Name) and although get total count of(Number) at the same query to get the following result: 

   Duplicate Count |Total Count
   --------------------------
   37            | 56

Upvotes: 1

Views: 1205

Answers (4)

merrais
merrais

Reputation: 401

select (select sum(Number) from Test 
    where Name in (select Name from Test group by Name having count(Name)>1))) as [Duplicate Count], 
(select sum(Number) from Test) as [Total Count]

Upvotes: 0

Boneist
Boneist

Reputation: 23588

Here's one way:

with sample_data as (select 'name1' name, 15 num from dual union all
                     select 'name1' name, 7 num from dual union all
                     select 'name2' name, 10 num from dual union all
                     select 'name3' name, 9 num from dual union all
                     select 'name4' name, 11 num from dual union all
                     select 'name4' name, 4 num from dual)
-- End of subquery mimicking a table called "sample_data" with data in it
-- See SQL below:                     
select sum(case when cnt_name > 1 then num end) duplicate_sum,
       sum(num) total_sum
from   (select name,
               num,
               count(*) over (partition by name) cnt_name
        from   sample_data);

DUPLICATE_SUM  TOTAL_SUM
------------- ----------
           37         56

Upvotes: 1

Utsav
Utsav

Reputation: 8103

Try this

WITH tbl
AS (
  SELECT name1
    ,sum(number1) AS sum1
    ,count(*) AS cnt
  FROM tz_Table9
  GROUP BY name1
  )
SELECT (
    SELECT sum(sum1)
    FROM tbl
    WHERE cnt > 1
    ) AS duplicate_count
  ,(
    SELECT sum(sum1)
    FROM tbl
    ) AS total_count
FROM dual

Upvotes: 1

fthiella
fthiella

Reputation: 49089

One solution is to use a window function to count the number of duplications per name on every row, then sum all values in one column, and sum only values where the record is duplicated on one other column:

with values_cnt as (
select
  count(*) over (partition by name) cnt,
  value
from
  mytable
)
select
  sum(case when cnt>1 then value end),
  sum(value)
from
  values_cnt

Upvotes: 2

Related Questions