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

CodePudding user response：

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,
         folio,
         date

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.