CODEWITHSUNDEEP
mysql
Roxx
Roxx

Reputation: 3986

MySQL: get average of value from query

I have 3 table.

Users --> UID, Name, Lat, Longg, Pic
Profile --> pid, uid,City, State, About
Feedback--> fid, uid, ratingby, txnid, rating, feedback_type

Feedback_type can be 1-4 from same txnid. So, if user is giving feedback for all the questions then there will be 4 records for same.

Now i need to show the user details along with the average feedback.

Below is the query i have written so far.

SELECT
                    a.name,
                    a.uid,
                    b.city,
                    a.pic,
                    b.state,
                    b.about
                FROM
                    users AS a
                INNER JOIN profile AS b
                ON
                    a.uid = b.uid

I am not sure how can i get the average value from feedback table.

I need show user average feedback and to be more specific. Average of all 4 feedback separately.

Also advise if my approach is good or is there any other best practice that i need to follow.

Edit

I can fetch the single record from feedback.

SELECT uid, avg(rating) FROM `feedback` WHERE uid= 8

But not sure how can i get the average for different feedback_type.

Upvotes: 1

Views: 90

Answers (2)

VietHTran
VietHTran

Reputation: 2318

You probably need to create a sub-table to find the average ratings of each type from the Feedbackback table (you can categorize feedback_type for calculating average by using group by f.uid, f.feedback_type). After that, you just need to join the resulting query table with the Users and Profile table to get additional data such as Name, City, etc.

SELECT u1.Name, ar.uid, ar.average_rating, ar.feedback_type, p.City, u1.Pic, p.State, p.About
FROM (
  SELECT f.uid, f.feedback_type, AVG(f.rating) AS average_rating
  FROM Feedback AS f
  WHERE f.uid=8
  GROUP BY f.uid, f.feedback_type
) AS ar
INNER JOIN Users AS u1 ON ar.uid=u1.UID
INNER JOIN Profile AS p ON ar.uid=p.uid;

Update: If alias is not working, an alternative approach would be to create a temporary table to calculate user's average rating and use the table to join with Users and Profile tables like above

CREATE TEMPORARY TABLE ar
SELECT uid, feedback_type, AVG(rating) AS average_rating
FROM Feedback
WHERE uid=8
GROUP BY uid, feedback_type;

SELECT Users.Name, ar.uid, ar.average_rating, ar.feedback_type, Profile.City, Users.Pic, Profile.State, Profile.About
FROM ar
INNER JOIN Users ON ar.uid=Users.UID
INNER JOIN Profile ON ar.uid=Profile.uid;

Update: If you need to put the records of 4 feedback types in different columns, you only need to group by uid in ar table and use CASE in AVG to filter out the feedback_type to calculate the average in each column

CREATE TEMPORARY TABLE ar
SELECT 
    uid,
    AVG(CASE WHEN feedback_type = 1 THEN rating END) AS average_rating_1,
    AVG(CASE WHEN feedback_type = 2 THEN rating END) AS average_rating_2,
    AVG(CASE WHEN feedback_type = 3 THEN rating END) AS average_rating_3,
    AVG(CASE WHEN feedback_type = 4 THEN rating END) AS average_rating_4
FROM Feedback
WHERE uid=8
GROUP BY uid;

SELECT
    Users.Name,
    ar.uid,
    ar.average_rating_1,
    ar.average_rating_2,
    ar.average_rating_3,
    ar.average_rating_4,
    ar.feedback_type,
    Profile.City,
    Users.Pic,
    Profile.State,
    Profile.About
FROM ar
INNER JOIN Users ON ar.uid=Users.UID
INNER JOIN Profile ON ar.uid=Profile.uid;

Upvotes: 2

Daniele Romanella
Daniele Romanella

Reputation: 94

You can get the avg value using this query SELECT AVG(column_name) FROM table_name WHERE condition;. To get more columns you should try

SELECT AVG(column_name_1) AS a, AVG(column_name_2) AS b, AVG() AS c, AVG() AS d FROM table_name WHERE condition

I hope i was helpful.

Upvotes: 0

Related Questions