CODEWITHSUNDEEP
mysqldatabase
DimmestDanger
DimmestDanger

Reputation: 11

How to get average from a column

I'm newbie with databases, and in school professor gave us homework to do.

I have the following tables:

games

id | game_id | game_name

categories

id | cat_name

helper

id | game_id | cat_id

prices

id | game_id | price | developer

I need to select average price for category with an id 2 and display result to loos like:

cat_name | avg_price

So this is my logic:

  1. First i need to find what games are assigned to category with id=2
  2. Then i need to select only games from table prices which are equal to result from the step 1
  3. Then i need somehow to write result as requested

So far this is my code:

select prices.price
from (select helper.game_id
from helper
where helper.cat_id="2")
where prices.game_id = helper.game_id

When I run it, in phpMyAdmin i receive error: Every derived table must have its own alias.

So how to assign that aliases (I already tried to look over internet, but im not sure if i understand them)?

Even better would if somebody give me ready code from which i can learn something.

Thanks!

Upvotes: 1

Views: 59

Answers (3)

Justin
Justin

Reputation: 9724

QUERY:

SQLFIDDLEExample

SELECT
c.cat_name,
AVG(price ) AS avg_price
FROM categories c
  LEFT JOIN helper h
    ON c.id = h.cat_id
  LEFT JOIN games g
    ON g.id = h.game_id
  LEFT JOIN prices p 
    ON p.game_id = g.id
WHERE c.id= 2
GROUP BY c.cat_name

Data:

INSERT INTO helper
    (`id`, `game_id`, `cat_id`)
VALUES
    (1, 1, 2),
    (3, 3, 1),  
    (2, 2, 2)
    INSERT INTO prices
        (`id`, `game_id`, `price`, `developer`)
    VALUES
        (1, 1, '12.5', 'EA'),
        (2, 2, '15.6', 'Woozie'),
        (3, 3, '25.6', 'Woozie')

Result:

| CAT_NAME | AVG_PRICE |
------------------------
|     good |     14.05 |

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

The error you are getting is because when you do a subselect like you have, you need to give that "derived" table that results from the subselect an alias like this:

select prices.price
from (select helper.game_id
from helper
where helper.cat_id="2") as helper_sub
where prices.game_id = helper_sub.game_id

Note the use of the alias in the WHERE clause as well since you are using that as the condition.

Now that I have answered the initial question, let me show you a better way to do this. You should be using a JOIN here, as subselects are typically not very optimal.

SELECT c.cat_name AS cat_name, AVG(p.price) as avg_price
FROM
categories AS c
INNER JOIN helper AS h ON c.id = h.cat_id
INNER JOIN prices AS p ON h.game_id = p.game_id
WHERE c.id = 2
GROUP BY c.id

Of course my assumption here is that there are indexes on all the "id" fields both where they are primary keys and foreign keys (like in the helper and prices tables). that is also why c.id is used for grouping rather than c.cat_name, as in a typical relational database you might not have an index on a field such as cat_name that is not being used for joins, to perform WHERE filtering, or for sorting purposes.

Upvotes: 1

Marc B
Marc B

Reputation: 360682

select prices.price
from (select helper.game_id
    from helper
    where helper.cat_id="2") AS subtable
                            ^^^^^^^^^^^^---alias for derived table
where prices.game_id = subtable.game_id
                       ^^^^^^^^---using the alias

Upvotes: 0

Related Questions