CODEWITHSUNDEEP
sqlaverage
Piotrek
Piotrek

Reputation: 29

SQL AVG function

What is the correct syntax to display the data from all records using GROUP (or DISTINCT) and AVG?

My data looks like this: TABLE NAME: Pensje COLUMNS: ID, Company, Position, Salary

Example:
ID    Company   Position   Salary
1      Atari      Designer   24000
2      Atari      Designer   20000
3      Atari      Programmer 35000
4      Amiga      Director   40000

I need to arrange data in this way (only records from 1 company need to be displayed)

Position a , average Salary from all the records with same Company and Position
Position b , average Salary from all the records with same Company and Position

Ex. Atari

Designer, 22000
Programmer, 35000

My SQL looks like this:

SELECT Position, AVG(Salary)
FROM Pensje
WHERE Company = %s
GROUP BY Position
ORDER BY Position ASC

In the above example "Position" is displayed correctly, "Salary" is not displayed at all, while after removing AVG() is displayed but only first position found in table

Many thanks for taking your time to help me!

Upvotes: 0

Views: 212

Answers (1)

Piotrek
Piotrek

Reputation: 29

Posting an answer from MCP_infiltrator which helped:

In your WHERE clause you should be using WHERE Company LIKE '%s' not = The way you have it written, should produce the results you want so I do not understand why it is not working properly for you. You may also want to alias your AVG() results like AVG(Salary) AS Average_Salary otherwise your column will comback with no name.

Upvotes: 1

Related Questions