CODEWITHSUNDEEP
mysql
Eis Zych
Eis Zych

Reputation: 13

Mysql Select SUM(Distinct)

I have a table in the database like below :

transDate ref total_price
2022-11-03 003 200
2022-11-05 003 200
2022-11-08 002 200
2022-10-16 001 150

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.

Year/date sum_of_total_price
2022-11 400
2022-10 150

Your kind help much appreciate.tq

Upvotes: 1

Views: 109

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Edit2, Emulate ROW_NUMBER , all credits to Sebastian Brosch

select DATE_FORMAT(transDate, '%Y-%m') as transDate ,
       sum(mt.total_price) as sum_of_total_price
from ( SELECT *, 
              IF(@prev <> ref, @rn:=0,@rn), @prev:=ref, @rn:=@rn+1 AS rn
       FROM my_table, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
       ORDER BY transDate ASC
     ) mt 
WHERE rn = 1
group by DATE_FORMAT(transDate, '%Y-%m');

https://dbfiddle.uk/6aASUamZ

Upvotes: 2

Related Questions