CODEWITHSUNDEEP
mysql
bhuwan
bhuwan

Reputation: 107

WITH UNION ALL unable to use ORDER BY

When I am running 

SELECT concat(name,'(',substring(occupation,1,1),')')  
FROM  occupations    
UNION ALL 
SELECT concat('There are total ',count(occupation),' ', occupation,'.') 
FROM occupations 
GROUP BY occupation

the query is working fine but when I add order by 

SELECT concat(name,'(',substring(occupation,1,1),')')  
FROM  occupations    
UNION ALL 
SELECT concat('There are total ',count(occupation),' ', occupation,'.')  
FROM occupations 
GROUP BY occupation 
ORDER BY name

this is showing an error:

Unknown column 'name' in 'order clause'

table occupations fields name,occupation

Upvotes: 0

Views: 145

Answers (4)

e4c5
e4c5

Reputation: 53734

SELECT concat(name,'(',substring(occupation,1,1),')') as name
FROM  occupations    
UNION ALL 
SELECT concat('There are total ',count(occupation),' ', occupation,'.') as name
FROM occupations GROUP BY occupation 
ORDER BY name

Update: Why this is so:
Because you don't have a common column named, name. When you apply a function to a query, mysql use a combination of that function name and parameters to create a column name for you. Which in reality means query will have a name of concat(name,'(',substring(occupation,1,1),')') (most probably upper cased) while the second will have concat('There are total ',count(occupation),' ', occupation,'.')

It doesn't matter that the names are different as far as Union is concerned because mysql is smart enough to combine them together by their data type. However when you add an order by clause you need a column name that's common to both.

Upvotes: 3

Lajos Arpad
Lajos Arpad

Reputation: 76436

Relational data model relies on relational algebra. All your tables are relations. Selection is a clojure operation on your relations, that is, selection takes relation(s) as input and has relation as output.

That being said, let's identify the relations used in your question. Everything relies on your relation called occupations. You are doing two selections with that relation. The first outputs a record-based textual output, the second outputs a grouped value based on operation. Your union is not aware of names, as it contains a single, nameless column and that nameless column will not match the column you call name.

There is a technically sound solution provided by several answerers, but that answer is logically unsound. We should not give name as alias to the nameless column of the second selection, as it clearly is a number. You need to do something else, like this:

select total
from
(SELECT concat(name,'(',substring(occupation,1,1),')') as name, 0 as total
FROM  occupations    
UNION ALL 
SELECT name, concat('There are total ',count(occupation),' ', occupation,'.') as total
FROM occupations 
GROUP BY occupation) t
order by name

Upvotes: 0

Mridul Kashyap
Mridul Kashyap

Reputation: 700

SELECT concat(name,'(',substring(occupation,1,1),')')  FROM  occupations    
UNION ALL SELECT concat('There are total ',count(occupation),' ', occupation,'.') FROM occupations GROUP BY occupation 
ORDER BY name

there is no column named name being returned in the above query.

You can use this instead.

SELECT concat(name,'(',substring(occupation,1,1),')') as name 
FROM  occupations    
UNION ALL 
SELECT concat('There are total ',count(occupation),' ', occupation,'.') as name 
FROM occupations 
GROUP BY occupation 
ORDER BY name

Upvotes: 3

Blank
Blank

Reputation: 12378

Add alias for your select column:

SELECT CONCAT(name,'(', SUBSTRING(occupation,1,1),')') as name
FROM occupations
UNION ALL
SELECT CONCAT('There are total ', COUNT(occupation),' ', occupation,'.') as name
FROM occupations
GROUP BY occupation
ORDER BY name

Upvotes: 3

Related Questions