CODEWITHSUNDEEP
sqlmysql
user2666633
user2666633

Reputation: 340

Issues with MySQL query

i have two tables 'reviews' and 'company'

//Company//

id | name      | others |
1  | company 1 | dhvhvbd|
2  | company 2 | didhhdi|
3  | company 3 | fyfyufu|
4  | company 4 | hdihdhi|

//Reviews*//

id | company_id | rate |
1  | 1          | 3    |
2  | 3          | 5    |
3  | 1          | 4    |

I am trying to write an SQL query like this (not correct but you should know what i want)

SELECT c.id
     , c.name
     , c.others
     , AVG(r.rate) rate
     , COUNT(*) count 
  FROM company c 
  LEFT 
  JOIN reviews r
    ON r.company_id = c.id
 GROUP 
    BY c.id;

This might look messy but this is what i want: to display all this listed company (irrespective of if they have a review in the review table), also get the average of 'reviews.rate' for each company (as they will be more than 1 review for one company) also get the count of the review where the company appears.

example:

display

id | name     | others | rate | count |
 1 | company 1| dhvhvbd| 3.5  | 2     |
 2 | company 2| didhhdi| NULL | NULL  |
 3 | company 3| fyfyufu| 5    | 1     |
 4 | company 4| hdihdhi| NULL | NULl  |

I hope its clear enough, i am just getting to learn some little bit complex queries

Upvotes: 0

Views: 50

Answers (2)

Alberto Solano
Alberto Solano

Reputation: 8227

Since you're using aggregate operators like COUNT and AVG, you have to group your records, by company.id, company.name and company.others (all the columns included in the SELECT clause). For this reason your query won't work.

Furthermore, using GROUP BY id your MySQL engine will complain, because actually the column id is ambiguous, because both tables involved in the LEFT JOIN have an id column and the engine doesn't understand which column of which table you mean.

Use the following query:

SELECT company.id, company.name, company.others, 
       AVG(reviews.rate) AS rate, 
       (CASE WHEN COUNT(reviews.id) = 0 THEN NULL ELSE COUNT(reviews.id) END) AS `count` 
FROM Company
LEFT JOIN Reviews ON company.id = reviews.company_id 
GROUP BY company.id, company.name, company.others

See the SQL Fiddle here.

Upvotes: 1

juergen d
juergen d

Reputation: 204766

You need to add all columns into the group by clause that won't get aggregated with a function

SELECT company.id, company.name, company.others, 
       AVG(reviews.rate) AS rate, 
       COUNT(reviews.id) AS `count` 
FROM company
LEFT JOIN reviews ON company.id = reviews.company_id 
GROUP BY company.id, company.name, company.others

And if you have 2 tables with the same column names then you need to name the table too. You used group by id in your query but the DB does not know which one to take - from reviews or from company.

Upvotes: 1

Related Questions