'MySQL Query error using UNION/UNION ALL and Group By

Generate the following two result sets:

1). Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

2). Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are total [occupation_count] [occupation]s.

Table Name: Occupations

Total Columns: Two = 'Name' and 'Occupation', demo table is shown below:

Table: Occupations

Sample Output:

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are total 2 doctors.
There are total 2 singers.
There are total 3 actors.
There are total 3 professors.

My Approach:

(SELECT NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')' 
    FROM OCCUPATIONS ORDER BY NAME) 
UNION ALL 
(SELECT COUNT(*) FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY ASEC);

Error:

ERROR 1222 (21000) at line 1: 
    The used SELECT statements have a different number of columns

Thank You!

Solution 1:^[1]

You forgot to use CONCAT function to glue your selected data

Try something like this (also see this example on sqlfiddle):

(
  SELECT CONCAT(NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')') as THETEXT, '1' as SELECTNUMBER
    FROM OCCUPATIONS
)
UNION ALL 
(
  SELECT CONCAT('There are total ', COUNT(*),' ', OCCUPATION, (IF (COUNT(*) > 1, 's',''))) as THETEXT, '2' as SELECTNUMBER
   FROM OCCUPATIONS GROUP BY OCCUPATION
)
ORDER BY SELECTNUMBER ASC, THETEXT ASC;

Solution 2:^[2]

I just tried on hackerrank and it works, You don't need to use Union.

select concat(name,'(',upper(substring(occupation,1,1)),')') from occupations
order by name;

select concat("There are a total of",' ',count(occupation),' ',lower(occupation),'s',".")  from occupations
group by occupation
order by count(occupation) asc;

Solution 3:^[3]

SELECT concat(NAME,concat("(",LEFT(occupation,1),")")) 
FROM OCCUPATIONS 
ORDER BY NAME ASC;

select CONCAT("There are a total of", " ",COUNT(occupation), " ",LCASE(occupation),"s",".")AS stat
from OCCUPATIONS
group by occupation
order by COUNT(occupation) ASC,occupation

Solution 4:^[4]

Worked on hackerrank

SELECT Name || '(' || SUBSTR(Occupation,1,1) || ')'-- AS THETEXT, '1' AS SELECTNUMBER
    FROM OCCUPATIONS
    order by Name;

    SELECT 'There are a total of' || ' ' || COUNT(*) || ' ' || lower(Occupation) || 's.'
    FROM OCCUPATIONS  GROUP BY OCCUPATION                            
    ORDER BY COUNT(Occupation), lower(Occupation);

Solution 5:^[5]

union requires the two queries have the same number of columns, you can add any some columns to the second query to make them like the first one like SELECT COUNT(*), 'col-1', 'col-2', 'col-3' from

Solution 6:^[6]

SELECT * FROM (
    select distinct Name + '('+left(Occupation,1)+')'as a
    from OCCUPATIONS 
    union
    select  ('There are total ' +cast( count( Occupation) as nvarchar) + ' '+ lower(Occupation) +'s.') as b 
    from OCCUPATIONS
    GROUP BY OCCUPATION
    ) AS N
ORDER BY a

This works for me on MS SQL Server.

Solution 7:^[7]

//Just be careful for the format!

(select concat(name, '(',left(occupation,1),')') as text
from occupations
)
union all
(
   select concat(
       'There are total ', 
       count(*), ' ', lower(occupation), 
       case
        when COUNT(*) > 1 
        then 's'
       end
       ,
       '.'
    ) as text
    from occupations
    group by occupation
)
order by text asc;

Solution 8:^[8]

(
  SELECT CONCAT(NAME, '(', SUBSTRING(OCCUPATION, 1, 1), ')') as THETEXT
    FROM OCCUPATIONS
)
UNION ALL 
(
  SELECT CONCAT('There are total ', COUNT(*),' ', LOWER(OCCUPATION) ,(IF (COUNT(*) > 1, 's','')), '.') as THETEXT
   FROM OCCUPATIONS  group by  OCCUPATION
)
ORDER by THETEXT ASC;

Solution 9:^[9]

This worked for me on MySQL

select concat(name,"(",substr(occupation,1,1),")") from occupations
order by name asc;

select concat('There are total ',count(*),' ',lower(occupation),'s.') as n from occupations
group by occupation
order by n asc;

Solution 10:^[10]

select concat(name,'(',left(occupation,1),')') from occupations order by name;


select concat('There are total of',' ',count(occupation),' ',lower(occupation),"s.") from occupations group by occupation order by count(occupation),occupation;

Solution 11:^[11]

(Select name||'('||substr(Occupation,0,1)||')'  
from OCCUPATIONS)
Union All
(Select 'There are a total of '||count(*)||' '||lower(Occupation)||'s.'  
from OCCUPATIONS group by Occupation) 
order by 1;

Solution 12:^[12]

SELECT CONCAT(name, '(', SUBSTRING(occupation, 1, 1), ')') as occ from occupations ORDER BY name;

SELECT concat('There are a total of ', COUNT(*), ' ', LCASE(occupation),  's.') from occupations GROUP BY occupation ORDER BY COUNT(*), occupation;

This does the charm.

Solution 13:^[13]

    SELECT CONCAT(NAME ,'('||SUBSTR(occupation,1,1)||')')
      FROM occupations 
  ORDER BY NAME ASC;

    SELECT CONCAT('There are a total of ',COUNT (occupation)||' '||LOWER(occupation)||'s.')
      FROM occupations 
  GROUP BY occupation 
  ORDER BY COUNT(occupation),occupation ASC;

Solution 14:^[14]

This works for me:

select CONCAT(name,'(',left(occupation,1),')') 
from OCCUPATIONS 
order by name;

select CONCAT('There are a total of',' ',count(occupation),' ',lower(occupation),'s.') 
from OCCUPATIONS 
group by occupation 
order by count(occupation), occupation;

Solution 15:^[15]

SELECT CONCAT(NAME,'(',LEFT(OCCUPATION,1),')')
FROM OCCUPATIONS ORDER BY NAME;

SELECT CONCAT("There are total of ",COUNT_OCCUPATION," ",OCCUPATION,".")
FROM
(SELECT COUNT(OCCUPATION) AS COUNT_OCCUPATION,OCCUPATION
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT_OCCUPATION ASC,OCCUPATION ASC) AS OCP

Solution 16:^[16]

select CONCAT(NAME, '(', left(OCCUPATION,1),')') from OCCUPATIONS ORDER BY NAME;

select CONCAT("There are a total of ", count(OCCUPATION), " ", lower(OCCUPATION),(if(count(OCCUPATION) >1,"s","")), ".") from OCCUPATIONS group by occupation order by count(OCCUPATION), OCCUPATION;

Solution 17:^[17]

select concat(name, '(',substring(occupation,1,1),')') from OCCUPATIONS order  by name;
select concat('There are a total of ',count(occupation),' ',lower(occupation),'s.') as 'sentence' from OCCUPATIONS group by occupation order by count(occupation), occupation,field(occupation,'doctor','singer','actor','professor');

Solution 18:^[18]

check below code. hope this helps its 100% working

select concat(Name,"(",left(Occupation,1),")")
  from occupations 
 order by Name;

select concat("There are a total of ",count(Occupation),' ',lower(occupation),"s.") 
  from Occupations
 group by occupation 
 order by count(Occupation)asc, Occupation;

Solution 19:^[19]

  /* sort answer is here */ 
     ( select concat(name,'(',left(occupation,1),')') as ocps from 
      Occupations) union 
     (select distinct concat('There are a total of 
     ',count(occupation),' ',lower(occupation),'s.') from Occupations 
     group by occupation)order by ocps;

Solution 20:^[20]

select Name+'('+substring(Occupation,1,1)+')' from occupations order by name asc;

select concat('There are a total of',' ', count(occupation),' ',Lower(occupation),'s','.') from Occupations group by Occupation order by count(occupation), occupation asc

Solution 21:^[21]

One can make use of the UNION operator in MySQL to union each part of the question.

NOTE: To combine the column names into one column the CONCAT() function can be used.

SELECT DISTINCT CONCAT(name, '(', left(occupation, 1), ')') AS n
from OCCUPATIONS
union
select concat('There are a total of ', (select count(*) from OCCUPATIONS group by occupation having occupation = 'doctor'), ' doctors.')
union
select concat('There are a total of ', (select count(*) from OCCUPATIONS group by occupation having occupation = 'singer'), ' singers.')
union
select concat('There are a total of ', (select count(*) from OCCUPATIONS group by occupation having occupation = 'actor'), ' actors.')
union
select concat('There are a total of ', (select count(*) from OCCUPATIONS group by occupation having occupation = 'professor'), ' professors.')

order by n asc;

Solution 22:^[22]

select concat(Name,'(',substring(Occupation,1,1),')') from OCCUPATIONS order by Name;

select 
concat('There are a total of ',count(*),' ',LCASE(Occupation),
       If(count(*)>1,'s.','')) 
       from OCCUPATIONS group by Occupation order by count(*) ASC
       ;

Solution 23:^[23]

Please follow this code for Mysql. This works best for me. I have tried and got the correct results.

select concat(name,'(',left(occupation,1),')') from occupations
order by name;

select concat('There are a total of ',count,' ',lower(occupation),'s.') from (select occupation, count(*) as count from occupations group by occupation)a

order by count, occupation;

For first query, I have concat the column 'name' with brackets and 1st char of value in column occupation. later order by is arranging them in ascending order (by default it is in ascending order).
second query, order by count(occupation) was not working for me so to solve this, i have used nested queries. Query in bracket is providing me occupation column, count of occupation when it is group by. then i concat the string with count and occupation name. later order by count in ascending order. Further if count is same for 2 occupations , so used order by occupation too to arrange them in ascending order.

Thanks

Solution 24:^[24]

SELECT concat(name,'(',left(occupation,1),')')
FROM occupations
ORDER BY name;

SELECT concat('There are a total of ',cnt,' ',lower(occupation),'s.') FROM (
  SELECT occupation,count(occupation) as cnt
  FROM occupations
  GROUP BY occupation
) a
ORDER BY cnt,occupation;

Solution 25:^[25]

You can use in this way:

 SELECT CONCAT(NAME,"(",SUBSTRING(Occupation, 1, 1),")") FROM OCCUPATIONS ORDER BY NAME
    SELECT CONCAT("There are a total of"," ",COUNT(Occupation),
    " ",LOWER(Occupation),"s",".") FROM OCCUPATIONS GROUP BY Occupation ORDER BY COUNT(Occupation),Occupation

Solution 26:^[26]

This works too, no need of union as they are asking for 2 separate queries

select name||'('||substr(occupation,1,1)||')' from occupations order by name;

select 'There are a total of ',cnt,decode(occ,'P','professors','D','doctors',
'S','singers','A','actors')||'.'
from
(select distinct(substr(occupation,1,1)) as occ,count(*) as cnt from occupations group by substr(occupation,1,1)) order by cnt,occ;

Solution 27:^[27]

select concat(name,"(",substring(occupation, 1, 1), ")") from occupations order by name;

select concat("There are a total of ", count(Occupation), " ", lower(occupation), "s.") from occupations group by occupation order by count(Occupation), Occupation;

Solution 28:^[28]

select concat(Name,'(',upper(substr(Occupation,1,1)),')') from OCCUPATIONS order by Name;
select concat('There are a total of',' ',count(Occupation),' ',lower(Occupation),'s','.') from OCCUPATIONS group by Occupation order by count(Occupation) asc,lower(Occupation);

P.S--- LOOK AT THE SECOND QUERY...ADD A FULLSTOP

Solution 29:^[29]

This works on Hacker rank, check this out

(select concat(name,'(',substr(occupation,1,1),')') N
from OCCUPATIONS) 
union
(select concat('There are a total of ',count(occupation),' ',lower(occupation),'s.') from 
occupations
group by occupation)
order by N asc;

Solution 30:^[30]

 WITH t1 AS (SELECT name, 
             LEFT(occupation,1) AS firstletter
             FROM occupations
             ORDER BY 1)
                              
SELECT CONCAT(name, '(', firstletter, ')') AS result
FROM t1;

WITH t2 AS (SELECT occupation,
            COUNT(occupation) AS occupation_count 
            FROM occupations
            GROUP BY 1
            ORDER BY 2,1)

SELECT CONCAT('There ','are ','a ','total ','of ',occupation_count,' ' ,LOWER(occupation),'s','.') 
FROM t2;

I have broken the question down into two CTE(Common table expression) which are temporary tables. The first CTE(t1) selects the occupation table for name, selects the first letter of the data in occupation column using "LEFT()" function then orders alphabetically by name as required by the question. The select statement then queries the CTE(t1) using "CONCAT()" function to obtain its result. CTE(t2) selects occupation, "COUNT()", group by occupation and order by occupation count and occupation. the select statement then queries t2 using "CONCAT()"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	user2654920
Solution 3	Rob
Solution 4	????? ????????
Solution 5	Nizar Ahmed
Solution 6	m00am
Solution 7	Shawn
Solution 8	Juan Serrats
Solution 9	rohan.k
Solution 10	samarth tyagi
Solution 11	Bogorovich
Solution 12	abhiagNitk
Solution 13	FanoFN
Solution 14	Kirti Nikam
Solution 15
Solution 16	Hassan Anwer
Solution 17	Plutian
Solution 18	MartenCatcher
Solution 19
Solution 20	Obsidian
Solution 21	Mostafa Nakhaei
Solution 22	Junaid Ahamad
Solution 23
Solution 24	Elikill58
Solution 25	Saman Salehi
Solution 26	Abhishek
Solution 27	Chris Catignani
Solution 28	Henry Ecker
Solution 29	Suraj Rao
Solution 30	faluta_vct

'MySQL Query error using UNION/UNION ALL and Group By

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]

Solution 17:[17]

Solution 18:[18]

Solution 19:[19]

Solution 20:[20]

Solution 21:[21]

Solution 22:[22]

Solution 23:[23]

Solution 24:[24]

Solution 25:[25]

Solution 26:[26]

Solution 27:[27]

Solution 28:[28]

Solution 29:[29]

Solution 30:[30]