How to count duplicate values as one value in sql query-CodePudding

I have a problem regarding of duplicate values in a SQL table column. I want to make sum of a column values but only count duplicates a once(something like merge them); something like below:

| Items    | value1 | Sub Items    | value2   |
| -------- | ------ |------------- | -------- |
| First    | 50     | First First  | 20       |
| First    | 50     | First Second | 50       |
| Second   | 20     | Second First | 50       |
| Second   | 20     | Second Second| 50       |
| Third    | 30     | Third First  | 50       |
| -------- | ------ |------------- | -------- |
| -------- | 100    |------------- | -------- |

I want to sum the values of column(value1) and the total summation must be 100. If I use SUM query it will count duplicates also and the result exceeds 100.

CodePudding user response：

You can remove distinct values in a subquery and then just add them. For example:

select sum(n) from (select distinct value1 as n from t) x

CodePudding user response：

You can use group by Items,value1 which will return only distinct values per Item and the doing the total sum in an outer query :

with cte as (
 select Items,value1
 from tbl
 group by Items,value1
) select sum(value1) as tot_distinct_sum
  from cte;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3e932750eace8125e695451900048cba

Written differently for MySQL version older than 8:

select sum(value1) as tot_distinct_sum 
from (select Items,
             value1
      from tbl
      group by Items,value1
      ) as t1;