Delete the duplicate values in the SUM with MySQL or SQL

Hi I am doing a sum of a table, but the problem is that the table has duplicate rows, so I wonder how can I do the sum without duplicated rows:

The main table is this one:

If I sum that you can see that the first and the third row are duplicated, so when I do the sum it makes it wrong because, the result will be:

but it should be:

The query that I use to make the sum is this one:

SELECT `jysparki_jis`.`api_transactions`.`cashier_id` AS `cashier_id`,
       SUM(`jysparki_jis`.`api_transactions`.`cash_amount`) AS `cash_amount`,,
       COUNT(0) AS `ticket_number`, 
       DATE(`jysparki_jis`.`api_transactions`.`created_at`) AS `date` 
FROM `jysparki_jis`.`api_transactions` 
WHERE DATE(`jysparki_jis`.`api_transactions`.`created_at`) >= '2022-01-01'     
  AND (`jysparki_jis`.`api_transactions`.`dte_type_id` = 39 
   OR `jysparki_jis`.`api_transactions`.`dte_type_id` = 61)    
  AND `jysparki_jis`.`api_transactions`.`cashier_id` <> 0 
GROUP BY `jysparki_jis`.`api_transactions`.`cashier_id`, 
         DATE(`jysparki_jis`.`api_transactions`.`created_at`)

How you can see the sum is this:

SUM(`jysparki_jis`.`api_transactions`.`cash_amount`).

I wonder how can I do the sum avoiding to duplicate the folio with same cashier_id?

I know that if I filter for the cashier_id and folio I can avoid the duplicate rows but I do not know how to do that, can you help me?

Thanks

>Solution :

Given your provided input tables, you can use the DISTINCT clause inside the SUM aggregation function to solve your problem:

SELECT cashier_id, SUM(DISTINCT amount)
FROM tab
GROUP BY cashier_id

Check the demo here.

Then you can add up your conditions inside your WHERE clause to this query, and your aggregation on the "created_at" field (that should correspond to the "date" field of your sample table – I guess). This solution may give your the general idea.