UNION ALL doesn`t return all available data

Databases
21

the solution is to apply an aggregate function to these columns

I had a view on “(Aggregate) Functions” in MySQL documentation and regard to your hints I choosed SUM() function for aggregation and my deduction was drived from this assumption :
Every ~NULL~ value in each group should be summed with its tantamount ~not NULL~ value .
So I modified the query like below and of course the drived result is correct now :agree:

SELECT   ft.leafCode
        ,ft.leafName
        ,ft.colorsCode
       [COLOR="Blue"] ,SUM(pack) AS `pack`[/COLOR]
       [COLOR="Blue"] ,SUM(warehouse) AS `warehouse`[/COLOR]
FROM (
   [COLOR="Silver"]     ---here are subqueries---[/COLOR]
       ) AS ft
      GROUP BY ft.leafCode
      ,ft.leafName
      ,ft.colorsCode;

Dear r937 , now final result is right but is my deduction right too ?

22

yes, except for one small detail – NULLs are ignored by aggregate functions

:slight_smile:

23

Ok , but now there is another question for me !
In above sample , warehouse and pack values have “double” property , but what about those values that are not “double” or “decimal” !? Let`s assume there is one column that its values are “string” .
Could you please guide me which (aggregate) functions could be used for those columns that have “string” values ? (regard to MySQL 5)

I am grateful for your unrivaled help :slight_smile:

24

use MAX() instead of SUM() – works on pack and warehouse in your example, too

25

Thanks :slight_smile: