Function SUM not working as expected

Question

I have two tables

Table devolucion

detalle_ticket_ticket_id         fecha
    20                        2013-06-04
    21                        2013-06-04
    23                        2013-06-04
    23                        2013-06-04
    23                        2013-06-04
    24                        2013-06-04
    24                        2013-06-04
    24                        2013-06-04

Table detalle_ticket

ticket_id     precio    iva   cantidad
   20          7.25      0       7.25
   21          20        0      20   
   23          12        0      12
   23          13        0      13
   23          14        0      14
   24          48.40     16     48.40
   24          18.50     16     18.50
   24          4.70      0       4.70

What I'm trying to do is to sum the values from the columns precio, iva, cantidad where the value from the column ticket_id is the same, I will explain better:

The first row in the column ticket_id is 20, the second is 21, the third is 23, the fourth is 23 so the third and fourth row have the same value (23) then what I need is to sum the respective values from the columns precio(values 12+13 = 25), iva (0+0=0), cantidad(12+13=25) and so on with the whole rows, the problem comes with my query which is not working as I expected. It keeps confusing me.

What I have tried so far

SELECT dt.ticket_id, SUM(dt.precio), SUM(iva), SUM(cantidad) 
FROM devolucion d INNER JOIN detalle_ticket dt 
ON d.detalle_ticket_ticket_id = dt.ticket_id 
WHERE DATE(d.fecha) = '2013-06-04'
GROUP BY dt.ticket_id;

What my (bad) query throws:

ticket_id     precio    iva   cantidad
   20          7.25      0       7.25
   21          20        0      20   
   23          117       0      12
   24          214.80   16      48.40  

What I expected:

ticket_id     precio    iva   cantidad
   20          7.25      0       7.25
   21          20        0      20   
   23          39        0      39
   24          71.6     32      71.6 

I have made it in sqlfiddle http://www.sqlfiddle.com/#!2/64367

Thank you in advance.

Answer 1

You can use a subquery to get the sum for each column in the detalle_ticket table, then you will join that subquery back to your devolucion table. Since you have multiple rows in each table and you are only joining on the ticket_id you can use the following:

SELECT distinct d.detalle_ticket_ticket_id, 
  dt.precio, 
  dt.iva, 
  dt.cantidad
FROM devolucion d 
INNER JOIN
(
  select ticket_id, 
    sum(precio) precio, 
    sum(iva) iva,
    sum(cantidad) cantidad
  from detalle_ticket
  group by ticket_id
) dt 
  ON d.detalle_ticket_ticket_id = dt.ticket_id 
WHERE DATE(d.fecha) = '2013-06-04';

See SQL Fiddle with Demo. This gives a result:

| DETALLE_TICKET_TICKET_ID | PRECIO | IVA | cantidad |
-----------------------------------------------------
|                       20 |   7.25 |   0 |  7.25    |
|                       21 |     20 |   0 |    20    |
|                       23 |     39 |   0 |    39    |
|                       24 |   71.6 |  32 |  71.6    |