Question 381385 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Unknown column [col_name] in 'order clause'

Select CONCAT ( Name, ‘(‘ , LEFT(occupation,1) , ‘)’ )
FROM occupations
UNION

Select
CONCAT (‘There are a total of ‘, Count(Occupation) ,’ ‘, occupation, ‘s’ ) as line
FROM occupations
Group by occupation

ORDER by line

Why can I not use ‘line’ in Order by even though it is in Select statement and gets executed before. I can use it correctly without the UNION but once I use UNION statement it is not working and I also tried Order by Count(occupation) it did not work

Tags:

2

Answers


  1. 0

    Try using the ORDER BY and GROUP BY clause in outer query for UNION. e.g.

    SELECT * FROM (
    SELECT
        CONCAT ( Name,  '(' , LEFT(occupation, 1) , ')' ) AS line, occupation
    FROM occupations
    UNION
    SELECT
        CONCAT ('There are a total of ', Count(Occupation) , ' ', occupation,'s' ) AS line, occupation
    FROM
        occupations
)T
GROUP BY T.occupation
ORDER BY T.line
    Login or Signup to reply.
  2. 0 
    CREATE TABLE occupations
(
   `occupation` VARCHAR(50),
   `Name` VARCHAR(50)
);

INSERT INTO occupations (`occupation`,   `Name` ) VALUES
  ('Writer','John'),
  ('Driver', 'Sam');

SELECT * FROM occupations;

SELECT * FROM
(
Select CONCAT ( Name, '(' , LEFT(occupation,1) , ')' ) as line, occupation, 1 as linetype
FROM occupations 

UNION
  
Select CONCAT ('There are a total of ', Count(Occupation) ,' ', occupation, 's' ) as line, occupation, 2 as linetype
FROM occupations 
Group by occupation
) t
ORDER BY line

    fiddle

    line occupation linetype
    Sam(D) Driver 1
    John(W) Writer 1
    There are a total of 1 Drivers Driver 2
    There are a total of 1 Writers Writer 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search