Since I am new to mysql .
select concat ((select to_char(count(*)) from users) ,' of users') from dual;
(or)
select concat ((select to_char(count(*)) from users) ,' of users') ;
I am expecting "100 of users" as result set output. in oracle, the above-mentioned statement will work , but not in mysql. i am getting result value as 1.
You can use the following, using CONCAT (or CONCAT_WS) and a CAST to char:
-- using CONCAT
SELECT CONCAT(CAST(COUNT(*) AS CHAR), ' of users') FROM users;
-- using CONCAT_WS
SELECT CONCAT_WS(' ', CAST(COUNT(*) AS CHAR), 'of users') FROM users;
Instead of TO_CHAR you need to use CAST(... AS CHAR(5)). Since MySQL 8.0 the CONCAT can concat the result of COUNT(*) and of users without a CAST or CONVERT (explicit conversion).
try below way it will works below is an example not directly yours
SELECT CONCAT(CAST(COUNT(*) AS CHAR(5)), ' of users') FROM Vendor;
