SQL Inner join with count

Question

I have two tables one called games and one called reviews.

I am trying to join these two tables together and have looked through the documentations and also the other questions here on stackoverflow.

SELECT games.id, games.title, games.developer, reviews.review, reviews.review_title,
(SELECT review, COUNT(*) 
FROM reviews
GROUP BY review) AS Numberofreviews 
FROM games
INNER JOIN reviews
ON games.ean=reviews.games_ean;

The query that i am trying to make is to get a table that shows the list of games and how many reviews each game has recieved.

But when i try implementing the above code i get the error operand should contain one column

Ive looked at other people getting this error but not in the same situation.

Any help would be apprecatied

edit: this is with mySQL

Consider providing proper DDLs and/or an sqlfiddle TOGETHER WITH THE DESIRED RESULT SET — Strawberry, Jan 13 '14 at 19:39

M Khalid Junaid · Answer 1 · 2014-01-13T19:49:53.550

1

You need to use the correlated subquery and for the error it clearly says there should be one column from your subquery

SELECT 
  g.id,
  g.title,
  g.developer,

  (SELECT 
    COUNT(*) 
  FROM
    reviews 
  WHERE games_ean = g.ean) AS Numberofreviews 
FROM
  games g
  INNER JOIN reviews r 
    ON g.ean = r.games_ean ;

edited Jan 13 '14 at 19:49

answered Jan 13 '14 at 19:39

M Khalid Junaid

63,861
10
90
118

where you have changed reviews to r is that just to shorten it right? – user3187726 Jan 13 '14 at 19:44
Not only to shorten it ,also to give an alias to refer it in subquery like `WHERE games_ean = r.games_ean` – M Khalid Junaid Jan 13 '14 at 19:45
With this it returns all the reviews which in this case is 5 for every column but im trying to get how many each game has – user3187726 Jan 13 '14 at 19:48
@user3187726 see my updated answer there was a mistake in comparsion – M Khalid Junaid Jan 13 '14 at 19:50
Ah yeh i did the same thing but realised that my WHERE was comparing with itself – user3187726 Jan 13 '14 at 19:57

Markus Malkusch · Answer 2 · 2014-01-14T18:45:43.933

1

You get the error "operand should contain one column" because your subquery returns two columns. Only one is allowed in this situation. However you don't need a subquery at all. Make a left join (in case for no reviews) and aggregate the reviews:

SELECT games.*, COUNT(reviews.games_ean)
  FROM games
  LEFT JOIN reviews ON reviews.games_ean = games.ean
  GROUP BY games.ean;

_{I assume that games.* is functional depended on games.ean (i.e. games.ean is unique).}

As this statement seems to cause confusion I want to emphasize that a projection on non aggregated attributes in a grouped statement is only defined for attributes which are functionally depended on the the grouped attributes. The statement (given the assumption that games.ean is unique) therefore is valid and makes perfect sense!

MySQL allows projections of non aggregated attributes which are not functionally depended! However they are not defined in ANSI-SQL nor are they deterministic as MySQL selects one undefined value for each non aggregated attribute.

edited Jan 14 '14 at 18:45

answered Jan 13 '14 at 19:46

Markus Malkusch

7,738
2
38
67

Please don't encourage the use of non-ANSI standard `GROUP BY`. – Kermit Jan 13 '14 at 20:06
[Why does MySQL add a feature that conflicts with SQL standards?](http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards) – Kermit Jan 13 '14 at 20:11
Sorry, but I think it is compliant. games.ean might not be the primary key but from the context of OP it seems to me that `games.*` is functional dependent on `games.ean`. Otherwise it wouldn't make no sense to use it as a foreign key. Please elaborate more where you see the conflict. – Markus Malkusch Jan 14 '14 at 13:08
Nothing more to elaborate. Take an ANSI standard DBMS and try it there. [I did it for you](http://sqlfiddle.com/#!3/0614f/2) – Kermit Jan 14 '14 at 14:03
@MarkusMalkusch I think you understand correctly. If that `games.ean` is a unique key, then the query would not be violating anything. I think you still need to add in the answer why this would be allowed in the case (where `game.ean` is a unique or primary key) and that it is not a good practice to follow in other cases or in general. – ypercubeᵀᴹ Jan 14 '14 at 14:54
Oh, and you need to correct that `COUNT(*)` to `COUNT(reviews.games_ean)` so the count is shown as `0` and not `1` when there is no matching row in the `review` table. – ypercubeᵀᴹ Jan 14 '14 at 14:55
You can certainly ask that as a new question, and I guarantee it will get closed. [This will probably shed more light on it](http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html) – Kermit Jan 14 '14 at 14:55
The *reason* your query violates *ANSI* SQL is because there are columns in your `SELECT` list (`games.*`) which are not aggregated or in the `GROUP BY` clause. The other columns in the `games` table are *not* functionally dependent (`title, developer`). – Kermit Jan 14 '14 at 15:14
If games.ean is unique in games the other columns are [functional depended](http://en.wikipedia.org/wiki/Functional_dependency) on games.ean. OP's context leads to the conclusion that [games.ean](http://en.wikipedia.org/wiki/International_Article_Number_%28EAN%29) is unique. I stated that assumption a few times. If you think that games.ean is not unique you could have said that. Your assertion that the given query violates ANSI SQL is wrong (and even phrased in your posted references). I'll assume you agree and will remove your downvotes plus your comments. – Markus Malkusch Jan 14 '14 at 16:03

score 0 · Accepted Answer · edited Jan 13 '14 at 20:03

0

You should move the subquery into the from clause, instead of reviews to get the number:

SELECT g.id, g.title, g.developer, r.Numberofreviews 
FROM games g inner join
     (SELECT games_ean, COUNT(*) as Numberofreviews
      FROM reviews
      GROUP BY games_ean
     ) r
     on g.ean = r.games_ean;

It does not make sense to have a column called review_title, because there might be more than one review.

edited Jan 13 '14 at 20:03

user3187726

75
1
1
6

answered Jan 13 '14 at 19:47

Gordon Linoff

1,242,037
58
646
786

The review_title column will come into effect later for users to be able to submit a quick summary of their review eg good/bad so that other users can then click it to get a more detailed review. That being said i didnt need to call that column here – user3187726 Jan 13 '14 at 20:04

Stafox · Answer 4 · 2014-01-13T20:04:03.260

-1

The query that i am trying to make is to get a table that shows the list of games and how many reviews each game has recieved.

SELECT games.id, games.title, games.developer, COUNT(DISTINCT reviews.*) as reviews_amount
FROM games
LEFT JOIN reviews ON games.ean = reviews.games_ean
GROUP BY games.ean;

edited Jan 13 '14 at 20:04

answered Jan 13 '14 at 19:45

Stafox

1,007
14
25

2

Beside that, `GROUP BY reviews.id` doesn't make sense. – Markus Malkusch Jan 13 '14 at 19:57
What does the DISTINCT call do – user3187726 Jan 13 '14 at 19:59
@user3187726 http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count – Stafox Jan 13 '14 at 20:02
Please don't encourage the use of non-ANSI standard `GROUP BY`. – Kermit Jan 13 '14 at 20:07

SQL Inner join with count

4 Answers4