how to add string or an of characters in mysql while using group by function

Question

For example, I have a table named XYZ and it has a column COUNTRY, how can I take out results in the following format using group by function,

INDIA has 3 employees

here 3 is opted from count() and INDIA is grouped by "GROUP BY", my question is that how do you print 'has' and employees in between and at the end in mySQl

I am learning DBMS in Oracle APEX. THANKS.

Answer 1

In MySQL / MariaDB you want

SELECT CONCAT_WS(' ', country, 'has', COUNT(*), 'employees')

In Oracle you want

SELECT country || ' has ' || COUNT(*) || ' employees

Edit This is plain old string processing. Every language, including every SQL dialect, has its own ways of doing string processing.

You can generate any sort of string you want, either with Oracle's || or MariaDB / MySQL's CONCAT_WS(). For your example, it's

SELECT CONCAT_WS(' ', COUNT(*), 'employees from', country)

or

SELECT COUNT(*) || ' employees from ' || country

Answer 2

In Oracle we can use || to concatenate strings in the SELECT.
If we want to list all the values in a GROUP BY we can use LISTAGG().
( In mySQL we can use CONCAT() or CONCAT_WS() for simple concatenation and GROUP_CONCAT() with GROUP BY. )

CREATE TABLE Employee (
id int, 
name varchar(10),
country varchar(10));

INSERT INTO Employee VALUES (1,'Tom','India');

INSERT INTO Employee VALUES (2,'Dick','India');

INSERT INTO Employee VALUES (3,'Harry','India');

SELECT country || ' has ' || COUNT(id) ||  ' employees' AS report
FROM Employee
GROUP BY country;

| REPORT                |
| :-------------------- |
| India has 3 employees |

SELECT country, LISTAGG(name,', ') Employees
FROM Employee
GROUP BY country;

COUNTRY | EMPLOYEES       
:------ | :---------------
India   | Tom, Dick, Harry

db<>fiddle here