I have a table in the database like below :

I had tried a few coding lines and below is one of them

SELECT YEAR(transDate) as year, 
       MONTH(transDate) as month,
       SUM(DISTINCT total_price) as sum_of_total_price  
  FROM table 
 GROUP BY YEAR(transDate), MONTH(transDate) 
 ORDER BY YEAR(transDate), MONTH(transDate) ASC

I want to SUM the total_price for every ref 003,002 and 001. But for ref:003 I just want to take 200 only because of duplicate value.

The final result I want to show by year and date like below.

Your kind help much appreciate.tq

CodePudding user response：

Use a subquery to get the distinct values, then aggregate on the outer query.

 select mt.transDate,
        sum(mt.total_price) as sum_of_total_price
 from  (select transDate,
               ref,
               total_price
        from my_table
        group by transDate,ref,total_price
       ) as mt
group by transDate;

https://dbfiddle.uk/dDNDFBjv

Edit. OP changed the logic of the question I will leave previous answer and add the updated query below:

select transDate ,
       sum(mt.total_price) as sum_of_total_price
from  ( select DATE_FORMAT(transDate, '%Y-%m') as transDate ,
               ref,
               total_price
        from my_table
        group by DATE_FORMAT(transDate, '%Y-%m'),ref,total_price
      ) as mt
group by transDate;

https://dbfiddle.uk/t5ydbLTV