Hello.

Why does SUM not summarize the result of UNION ?:

My request returns: UNION grouping query result

Given that the SUM(count) UNION query without grouping gives the correct count: enter image description here

SQL query itself:

 SELECT a.code_spec_product, a.code_spec_detail, a.id_subdivision, SUM( CAST('SUM(count)' AS SIGNED) ) FROM ( SELECT code_spec_product, acc.id_subdivision, code_spec_detail, SUM(count) FROM conn_accep_detail INNER JOIN acceptance acc ON acc.number = num_acc WHERE acc.id_subdivision != 9 GROUP BY code_spec_product, code_spec_detail, acc.id_subdivision UNION ALL SELECT code_spec_product, acc.id_subdivision, code_spec_detail, SUM(count) FROM connacc_detail INNER JOIN acceptance acc ON acc.number = num_acc WHERE acc.id_subdivision != 9 GROUP BY code_spec_product, code_spec_detail, acc.id_subdivision ) a GROUP BY a.code_spec_product, a.id_subdivision, a.code_spec_detail

UPDATE Request for the result I want: Correct result

 SELECT a.code_spec_product, a.code_spec_detail, a.id_subdivision, SUM(total) FROM ( SELECT code_spec_product, acc.id_subdivision, code_spec_detail, SUM(count) AS total FROM conn_accep_detail INNER JOIN acceptance acc ON acc.number = num_acc WHERE acc.id_subdivision != 9 GROUP BY code_spec_product, code_spec_detail, acc.id_subdivision UNION ALL SELECT code_spec_product, acc.id_subdivision, code_spec_detail, SUM(count) AS total FROM connacc_detail INNER JOIN acceptance acc ON acc.number = num_acc WHERE acc.id_subdivision != 9 GROUP BY code_spec_product, code_spec_detail, acc.id_subdivision ) a GROUP BY a.code_spec_product, a.id_subdivision, a.code_spec_detail

    1 answer 1

    That's right, this is due to the fact that you use the design

     GROUP BY a.code_spec_product, a.id_subdivision, a.code_spec_detail

    Since you have the same amount, you have two of the three records are the same and they are grouped. From three records it turns out 2. You will collect GROUP BY receive one record.

    Assign a SUM(count) alias to an AS using, for example,

     SUM(count) AS total

    and add grouping this column

     GROUP BY a.code_spec_product, a.id_subdivision, a.code_spec_detail total

    If you need the sum of all columns, remove the grouping altogether.

    • Only the total amount of the combined columns is needed. Your advice helped, thanks. I will write the final sql query in the question. - RostD