asked on

mysql union

When I use a mysql union my results are not being combined.

        (
         SELECT
            LOWER(`countries`.`country`) AS 'country',
            COUNT(LOWER(`countries`.`country`)) AS 'total',
            COUNT(CASE WHEN DATE_FORMAT(`charters`.`start_date`, '%Y') = '$year1' THEN `charters`.`start_date` END) AS '$year1',
            COUNT(CASE WHEN DATE_FORMAT(`charters`.`start_date`, '%Y') = '$year2' THEN `charters`.`start_date` END) AS '$year2',
            COUNT(CASE WHEN DATE_FORMAT(`charters`.`start_date`, '%Y') = '$year3' THEN `charters`.`start_date` END) AS '$year3',
            COUNT(CASE WHEN DATE_FORMAT(`charters`.`start_date`, '%Y') = '$year4' THEN `charters`.`start_date` END) AS '$year4',
            COUNT(CASE WHEN DATE_FORMAT(`charters`.`start_date`, '%Y') = '$year5' THEN `charters`.`start_date` END) AS '$year5'

....

     )
     UNION
     (
         SELECT
            LOWER(`db2`.`countries`.`name`) AS 'country',
            COUNT(LOWER(`db2`.`countries`.`name`)) AS 'total',
            COUNT(CASE WHEN DATE_FORMAT(`db2`.`charters`.`begin_date`, '%Y') = '$year1' THEN `db2`.`charters`.`begin_date` END) AS '$year1',
            COUNT(CASE WHEN DATE_FORMAT(`db2`.`charters`.`begin_date`, '%Y') = '$year2' THEN `db2`.`charters`.`begin_date` END) AS '$year2',
            COUNT(CASE WHEN DATE_FORMAT(`db2`.`charters`.`begin_date`, '%Y') = '$year3' THEN `db2`.`charters`.`begin_date` END) AS '$year3',
            COUNT(CASE WHEN DATE_FORMAT(`db2`.`charters`.`begin_date`, '%Y') = '$year4' THEN `db2`.`charters`.`begin_date` END) AS '$year4',
            COUNT(CASE WHEN DATE_FORMAT(`db2`.`charters`.`begin_date`, '%Y') = '$year5' THEN `db2`.`charters`.`begin_date` END) AS '$year5'

   ...
   )
  ORDER BY `country` ASC

Open in new window

However, what is happening is this:
Country, Total, 2010, 2011, 2012, 2013, 2014
australia       29       5       13       11       0       0
australia       30       0       0       0       7       23
austria       3       2       1       0       0       0
austria       4       0       0       0       1       3
belgium       10       3       2       5       0       0
belgium       8       0       0       0       3       5

I want each to sum together.

Ray Paseur

Not sure exactly what you want to get here, but when you want combined results the right clause is often GROUP BY.
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Robert Saylor

ASKER

In both my select statements I am using a group by...

(select ... where ... group by country) union (select ... where ... group by country) order by country

I have to use a union because I am pulling similar data from two different databases.

Robert Saylor

ASKER

I ended up putting the data into an array then was able to combine data-sets. But I still don't understand why the union returned the data un grouped even though it was grouped.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Saylor

ASKER

didn't want to post the entire query to protect information but with PHP I was able to get the job done.

A great programmer recognizes good-enough programming. That's your answer and it's good for me :)

Ray Paseur

FWIW, I often use PHP to do things that I could do in MySQL. For one thing, the code has to run somewhere, so there is not really any great savings in moving the code to the database engine. And for another, I'm smarter about PHP than I am about MySQL, so there is personal prejudice involved :-)

Thanks for the points and best of luck with it, ~Ray