MYSQL - AVG Function with $ sign

user_14753496 0
Viewed 40

i need to make a query that shows the average of each employee salary and it needs to be formatted with the $ sign. this is what i have and where im stuck at, any feedback?

SELECT CONCAT(FirstName, ' ', LastName) AS 'Employee Full Name',
AirportName, CONCAT('$',FORMAT(Salary,2)) AS 'Average Salary', 
MobileNumber, Email
FROM employees GROUP BY FirstName;

also when i add the AVG(salary) where the format is, it only shows one employee salary and not all of them

mysql
1 Answers

You might want to try something like this instead:

SELECT
  CONCAT(FirstName, ' ', LastName) AS `Employee Full Name`,
  AirportName,
  mobilenumber,
  email,
  CONCAT('$',FORMAT(avg(Salary),2)) AS `Average Salary`
FROM employees
GROUP BY
  CONCAT(FirstName, ' ', LastName),
  AirportName,
  mobilenumber,
  email;

Example

create table employees (
  firstname text,
  lastname text,
  airportname text,
  mobilenumber text,
  email text,
  salary decimal(10,2)
);
insert into employees values 
('matt', 'jensen', 'airport-a', '111', 'a@b.com', 100.50),
('matt', 'jensen', 'airport-a', '111', 'a@b.com', 200.50),
('pinny', 'donna', 'airport-b', '222', 'c@d.com', 50.50),
('pinny', 'donna', 'airport-b', '222', 'c@d.com', 350.50),
('golo', 'prada', 'airport-a', '333', 'z@x.com', 10.50);

Query's result

Employee Full Name | AirportName | mobilenumber | email   | Average Salary
:----------------- | :---------- | :----------- | :------ | :-------------
golo prada         | airport-a   | 333          | z@x.com | $10.50        
matt jensen        | airport-a   | 111          | a@b.com | $150.50       
pinny donna        | airport-b   | 222          | c@d.com | $200.50

See this: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3030e55183dd1bbd769a9567f48ab282

Explanation

Include all your non-statistics columns in your group by. Doing so, you are telling the database that for a unique combination of full name, airport, cell and email, you want average salary.

Your concat idea seems to be just fine. You'd just have to make sure you are concatenating $ with formatted average salary. Basically, you were on the right track.

user_25545370
Related