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

Mysql – count(*) querry

I tried to solve a problem code in Hackerrank.
An OCCUPATIONS table that contains the following records:

and here are for the sampel output:
Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

why that code doesnt work

select Concat(Name, '(', Substring(Occupation,1,1), ')') as pr
from Occupations
order by name;

select Concat('There are a total of',' ', count(*),' ',Occupation, 's.') as pr
from Occupations
group by Occupation
order by pr
Tags:

3

Answers


  1. 0

    Your approach of solving a problem was good. You can use this approach and it works in mysql but it doesn’t work in oracle.
    Because, in Oracle, CONCAT function can take a maximum of 2 arguments to concatenate where as in mysql, CONCAT function can take multiple arguments.
    You can use nested concat statements as follows:

    select concat(concat(‘There are a total of ‘, count(*)), concat(‘ ‘, occupation)) as pr
    from occupations
    group by occupation
    order by pr;

    Else, use || to concat multiple strings as below:

    select (‘There are a total of ‘|| count(*) || ‘ ‘ || occupation || ‘s’) as pr
    from occupations
    group by occupation
    order by pr;

    Login or Signup to reply.
  2. 0

    To solve the given question, you would need to create columns from the records in the table, but, SQL doesn’t have a native PIVOT function like some other technologies, though we can simulate it. Here’s one way to pivot our data:

    SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor
FROM
    (SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
     FROM Occupations) AS sub
GROUP BY rn
ORDER BY rn;

    The code you’ve written serves a different purpose than the task of pivoting the table as described in the question.

    Login or Signup to reply.
  3. 0

    I think you need to add only GROUP_CONCAT function

    SELECT GROUP_CONCAT(concat(Name, '(', Substring(Occupation, 1, 1), ')') SEPARATOR ' ') pr FROM Occupations; 

SELECT GROUP_CONCAT(Concat('There are a total of ', cnt,' ', Occupation, '.') SEPARATOR ' ') pr FROM (
    SELECT Occupation, count(*) cnt FROM Occupations GROUP BY Occupation
) t

    Try it here https://dbfiddle.uk/5dNfq0Ch

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search