Select from Two tables last name first name and avg salary in descending order by last name

Question

Actor table

Castings table

I am new to SQL and am stuck on a problem I am working on. I am wanting to display the last name, first name and avg salary of an actor. The salary and the actor's name is in two different tables. , Also I would like to display the results in descending order according to the actors last name. Here is what I have written up but I cannot get it to compile correctly. I have provided a screenshot of what I have so far.

Could you provide your tables info? you use `JOIN` but didn't use `on` — D-Shih, Sep 16 '18 at 08:02
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — Madhur Bhaiya, Sep 16 '18 at 08:02
Definitely not a duplicate, @D-Shih I have revised and added my actor and castings table — Codizzel01, Sep 16 '18 at 08:05
@lucumt please don't encourage the provision of pictures where raw, formatted text would do just as well. — Strawberry, Sep 16 '18 at 08:10

D-Shih · Accepted Answer · 2018-09-16T08:16:57.957

1

You use JOIN but didn't use on to connect two table, from your tables you might use actorID columns to be the connected condition.

when you use an aggregate function you might use non-aggregate columns in group by

SELECT a.lname,a.fname,AVG(c.salary)
FROM Actor a 
JOIN Castings c on a.actorID = c.actorID 
group by a.lname,a.fname
order by a.lname desc

Here is a link talking about JOIN

edited Sep 16 '18 at 08:16

answered Sep 16 '18 at 08:07

D-Shih

44,943
6
31
51

thank you for your help. This worked great. I was hoping you could help with one more thing. I am trying to use a Subquery where I can select the salary for an actor where I "forgot" their actorId but know there name. Anything I try gives me syntax errors – Codizzel01 Sep 16 '18 at 08:11
1

SELECT salary FROM Castings WHERE salary = (SELECT fname FROM Actor WHERE fname = 'Paul'); – Codizzel01 Sep 16 '18 at 08:12
1

I think ou need to use `join` rather than subquery `SELECT a.lname,a.fname,c.salary FROM Actor a JOIN Castings c on a.actorID = c.actorID WHERE a.fname = 'Paul' ` – D-Shih Sep 16 '18 at 08:13

Select from Two tables last name first name and avg salary in descending order by last name

1 Answers1