MySQL ‘Column X cannot be null’ When Performing UNION ALL

July 18th, 2012 - Posted by Steve Marks to MySQL, Web Development.

Whilst recently trying to run a query that involved a UNION ALL, I got an error claiming that a column could not be NULL. Running each of the queries individually worked fine so I knew I had to dig a bit deeper.

Here’s a simplified version of my query:

	`amount1` AS amount


	SUM(`amount2`) AS amount

The Solution

It turns out that the error was caused because of the aggregate SUM() function in the second query. Adding a GROUP BY to the query prevented the error and returned the resultset as expected.

This entry was posted on Wednesday, July 18th, 2012 at 7:50 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.

Fear not, we won't publish this

Comments (1)
  1. Enor says:

    I had the same issue but instead of SUM() I had to use MAX() and to solve it I used in the second query HAVING name_of_column = MAX(name_of_column)