CODEWITHSUNDEEP
sqljoinsum
TheDon
TheDon

Reputation: 1

Incorrect SUM when I JOIN another table

I am trying to apply discounts to various totals. Each discount has its own discount code so I want to apply the code to its equivalent total. But sometimes this code can appear more than once. If so, I want to sum the discount codes to produce one discount.

My first attempt was this which was not correct but shows exactly what I just tried to explain, ie. discount codes appearing more than once:

SELECT bdto.dto_fac_cod_descuento,
    COUNT(bdto.dto_fac_cod_descuento) howmany,
    CASE
        WHEN COUNT(bdto.dto_fac_cod_descuento) > 1
            THEN ABS (ROUND (SUM(bdto.dto_fac_importe_dto), 0))
            ELSE ABS (ROUND (bdto.dto_fac_importe_dto, 0))
            END AS descuento
    FROM 
         bren_descuentos bdto
GROUP BY bdto.dto_fac_cod_descuento, bdto.dto_fac_importe_dto


dto_fac_cod_descuento   howmany descuento
714         1   4274.00
X23         1   4040.00
X23         1   3300.00
X23         1   2800.00
336         1   2584.00
E35         1   2519.00
713         1   1458.00
335         1   920.00
G07         1   610.00
M48         1   350.00
715         1   310.00
368         2   450.00
G07         1   94.00
168         1   70.00
349         1   62.00
X73         1   20.00
BN3         1   10.00
M47         1   2.00
A40         2   0.00
S11         1   0.00

After much deliberation, I currently have the following query which gives me the right answer (I doubt the code is the best it could be but that doesn't concern me right now)

SELECT bdto.dto_fac_cod_descuento,
            CASE
        WHEN COUNT(bdto.dto_fac_cod_descuento) > 1
            THEN 
                ABS (ROUND (SUM(bdto.dto_fac_importe_dto), 0))
            ELSE 
                (SELECT ABS (dto_fac_importe_dto) FROM bren_descuentos bd
                    WHERE bdto.dto_fac_cod_descuento = bd.dto_fac_cod_descuento) 
        END AS descuento
    FROM 
         bren_descuentos bdto
GROUP BY bdto.dto_fac_cod_descuento 

dto_fac_cod_descuento   descuento
168         70.44
335         919.85
336         2584.31
349         62.16
368         450.00
713         1458.05
714         4273.73
715         309.62
A40         0.00
BN3         10.00
E35         2519.00
G07         704.00
M47         2.46
M48         349.77
S11         0.00
X23         10140.00
X73         20.00

My problem is I need to join another table as this query is just part of what I need and when I apply the JOIN, my results go haywire and I dont know how to fix it!

With the JOIN applied I get this:

SELECT bdto.dto_fac_cod_descuento,
            CASE
        WHEN COUNT(bdto.dto_fac_cod_descuento) > 1
            THEN 
                ABS (ROUND (SUM(bdto.dto_fac_importe_dto), 0))
            ELSE 
                (SELECT ABS (dto_fac_importe_dto) FROM bren_descuentos bd
                    WHERE bdto.dto_fac_cod_descuento = bd.dto_fac_cod_descuento) 
        END AS descuento
    FROM bren_detalle bdet
         JOIN
         bren_descuentos bdto
ON bdet.det_unidad_medida = 'megabytes'
GROUP BY bdto.dto_fac_cod_descuento

dto_fac_cod_descuento   descuento
168         318177.00
335         4154962.00
336         11673328.00
349         280777.00
368         2032650.00
713         6586012.00
714         19304438.00
715         1398554.00
A40         0.00
BN3         45170.00
E35         11378323.00
G07         3179968.00
M47         11112.00
M48         1579911.00
S11         0.00
X23         45802380.00
X73         90340.00

@Dukeling "Full" code with JOIN:

SELECT bdet.det_tipo_trafico tipo_trafico, 
        COUNT (bdet.det_tipo_trafico) total_numero, 
        ROUND (SUM (bdet.det_cantidad_medida_originada + bdet.det_cantidad_medida_recibida), 0) total_megas,
        ROUND (SUM (bdet.det_importe), 2) total_importe,
        ABS (ROUND (SUM(bdto.dto_fac_importe_dto), 0)) AS descuento,
        ROUND ((SUM (bdet.det_cantidad_medida_originada + bdet.det_cantidad_medida_recibida)) / NULLIF (COUNT (bdet.det_tipo_trafico), 0), 0) mb_conxn
    FROM bren_detalle bdet
        JOIN
         bren_descuentos bdto
ON bdet.det_unidad_medida = 'megabytes'
    AND bdet.dto_fac_cod_descuento = bdto.dto_fac_cod_descuento
GROUP BY bdet.det_tipo_trafico, bdto.dto_fac_importe_dto, bdto.dto_fac_cod_descuento

Gives this result:

tipo_trafico            total_numero    total_megas total_importe   descuento   mb_conxn
DATOS EN ITINERANCIA        224     2176        653,88      0.00        10
MENSAJES MULTIMEDIA EN ITINERA  1       0       0,7     10.00       0
DATOS INTERNET          4389        38338       1789,19     412566.00   9
MENSAJES MULTIMEDIA     15      2       6,36        37785.00    0
DATOS INTERNET          4389        38338       1789,19     2677290.00  9

As you can see I get two "DATOS INTERNET" because there are two different values for descuento, it diesnt sum them. Also it still gives a HUGE number for the discounts, it is summing too many times.

Example of contents of bren_detalle (there are more columns but I am not using them)

det_tipo_trafico            det_unidad_medida   det_importe dto_fac_cod_descuento   dto_fac_cod_descuento2
TRAFICO NAC.OTROS OPER.MOVILES      Minutos         3,6588          714         368
TRAFICO NAC.OTROS OPER.MOVILES      Minutos         1,4035          714         368
DATOS INTERNET  Megabytes   0,0583  G07 NULL
TRAFICO NAC.OTROS OPER.MOVILES  Minutos 8,756   714 368
TRAFICO NAC.OTROS OPER.MOVILES  Minutos 0,5195  714 368
DATOS INTERNET  Megabytes   0,0097  G07 NULL
INTERNO CORPORATIVO Minutos 0,1758  335 368
INTERNO CORPORATIVO Minutos 0,2617  335 368
TRAFICO NAC.OTROS OPER.MOVILES  Minutos 1,8313  714 368
INTERNO MOVILES Minutos 1,5993  336 368
TRAFICO NAC.OTROS OPER.MOVILES  Minutos 1,2165  714 368
INTERNACIONAL   Minutos 1,541   M48 NULL
TRAFICO NAC.OTROS OPER.MOVILES  Minutos 1,2108  714 368
DATOS INTERNET  Megabytes   0,3351  G07 NULL
DATOS INTERNET  Megabytes   0,9028  G07 NULL
DATOS INTERNET  Megabytes   0,0102  G07 NULL
RESTO DE TRAFICO NACIONAL   Minutos 2,4196  715 368
DATOS INTERNET  Megabytes   0,0271  G07 NULL
TRAFICO NAC.OTROS OPER.MOVILES  Minutos 2,8372  714 368
DATOS INTERNET  Megabytes   0,1574  G07 NULL

Contents of bren_descuentos table (again there are more columns but not used):

dto_fac_cod_descuento   dto_fac_importe_dto
S11 0.00
A40 0.00
BN3 -10.00
G07 -94.00
X23 -4040.00
168 -70.44
335 -919.85
336 -2584.31
349 -62.16
368 -225.00
368 -225.00
713 -1458.05
714 -4273.73
715 -309.62
A40 0.00
E35 -2519.00
M47 -2.46
M48 -349.77
X23 -3300.00
G07 -610.00
X23 -2800.00
X73 -20.00

To give an example, discount code G07 goes with DATOS INTERNET traffic so I should have 610+94=704 total discount.

Upvotes: 0

Views: 208

Answers (1)

Bernhard Barker
Bernhard Barker

Reputation: 55619

It does depend what bren_detalle looks like, but you probably need to specify a field to JOIN on (otherwise it will basically be a CROSS JOIN - one row matched to each other row in the other table):

bren_detalle bdet
JOIN bren_descuentos bdto
  ON bdet.somefield = bdto.someotherfield
  AND bdet.det_unidad_medida = 'megabytes'

Note that the reason you were getting unwanted results in the first query is because of dto_fac_importe_dto in the GROUP BY. This would result in a row for every distinct combination of dto_fac_cod_descuento and dto_fac_importe_dto. Just remove it and it should work: (also the CASE seems a bit redundant)

SELECT bdto.dto_fac_cod_descuento,
    COUNT(bdto.dto_fac_cod_descuento) howmany,
    ABS (ROUND (SUM(bdto.dto_fac_importe_dto), 0)) AS descuento
FROM bren_descuentos bdto
GROUP BY bdto.dto_fac_cod_descuento

EDIT:

The problem is the your tables looks something like:

table 1    table 2
f1 f2      f1 f3
1  8       1  10
1  9       1  11

Then, when you JOIN on f1 you get 4 rows, one for each combination of rows matching. Thus SUM(f2) = (8+9)*2 (2 rows in table 2).

SQLFiddle.

There may be more efficient ways, but the only way I can think of right now to fix it:

...
FROM (SELECT dto_fac_cod_descuento, SUM(someField), SUM(someOtherField)
      FROM bren_detalle
      WHERE det_unidad_medida = 'megabytes'
      GROUP BY dto_fac_cod_descuento) bdet
  JOIN
     (SELECT dto_fac_cod_descuento, SUM(someField), SUM(someOtherField)
      FROM bren_descuentos
      GROUP BY dto_fac_cod_descuento) bdto
    ON bdet.dto_fac_cod_descuento = bdto.dto_fac_cod_descuento

Upvotes: 1

Related Questions