How will I be able to calculate the percentage difference between the columns Average and Movie_Cost? - Phpmyadmin

HassanZaidi
March 31, 2020
127 views
0 votes
2 Answers

 a.Movie_Cost, a.Movie_Num,b.Movie_Genre,b.Average
FROM
 movie AS a
iNNER JOIN(
 SELECT
     Movie_Genre,
     AVG(Movie_Cost) AS Average
 FROM
     movie
 GROUP BY
     Movie_Genre
) AS b
ON
 a.Movie_Genre = b.Movie_Genre

This query gives out the result shown in the picture:

What I am trying to do here is simple, find out what each genre’s average cost is and then find the percentage difference between the columns Average and Movie_Cost. Each genre’s average cost is calculated using the Group BY clause and AVG function, as you can see in the query.
I think the main problem I face is that I am not able to reuse the average column which I generated, in order to calculate the percentage difference.
Btw I am using PHPmyAdmin
Also when I tried implementing it myself it looked something like this:

SELECT
    a.Movie_Cost, a.Movie_Num,b.Movie_Genre,b.Average, (SELECT 100*(a.Movie_Cost-b.Average)/b.Average FROM a INNER JOIN b WHERE a.Movie_Genre=b.Movie_Genre)
FROM
    movie AS a
iNNER JOIN(
    SELECT
        Movie_Genre,
        AVG(Movie_Cost) AS Average
    FROM
        movie
    GROUP BY
        Movie_Genre
) AS b
ON
    a.Movie_Genre = b.Movie_Genre

But this doesn’t work because of issues with the aliases. It gives me the error that the table ‘a’ does not exist.

Tags: sql

Answers

SELECT
    a.Movie_Cost, a.Movie_Num,
b.Movie_Genre,b.Average,
    100*(a.Movie_Cost-b.Average)/b.Average 
FROM
    movie AS a
iNNER JOIN(
    SELECT
        Movie_Genre,
        AVG(Movie_Cost) AS Average
    FROM
        movie
    GROUP BY
        Movie_Genre
) AS b
ON
    a.Movie_Genre = b.Movie_Genre

- GordonLinoff
- March 31, 2020 at 3:38 pm
- 0 votes
0
Use window functions:
```
SELECT m.*,
       (m.movie_cost - AVG(m.movie_cost) OVER (PARTITION BY movie_genre)) as diff
FROM movie m;
```
EDIT:

You can do any arithmetic you want, for instance:
```
SELECT m.*,
       (m.movie_cost * 100.0 /
        AVG(m.movie_cost) OVER (PARTITION BY movie_genre)
       ) - 100 as percentage
FROM movie m;
```
Login or Signup to reply.