1

In my MySQL database I got three tables. One is a list of items having an ID and some more columns. The second one is a list of users with first name and so on. The third on records if a user has rated and/or bought an item. The data type of the column for rated and bought will be a bit-flag (1/0).

rating table

So, now I would like to return in only one SQL query how often an item was rated and if it was rated by the specific users.

Input arguments are:

  • user
  • item

Result should be:

  • count(rated)
  • true/false(rated by specified user)

enter image description here

Matthias
  • 5,574
  • 8
  • 61
  • 121
  • The number of users (which means the number of columns in the resultset) should be dynamic ? – Raphaël Althaus Sep 11 '14 at 13:08
  • The second picture is just for understanding. Let's say I query with an SQL statement for item=1 and user=1. Then I would like to get in return how often the item has been rated (by all users) and if user 1 has rated item 1 or not. I found this one: **select count(*), count(case when user = 1 then 1 else null end) from rating where item = 1** But is there a shorter way? – Matthias Sep 11 '14 at 13:09

1 Answers1

2

This is called a pivot.

SELECT item, SUM(rated) AS no_rated, SUM(bought) AS no_bought,
        MAX(user = 1 AND rated = 1) AS rated_by_1,
        MAX(user = 2 AND rated = 1) AS rated_by_2,
        MAX(user = 1 AND bought = 1) AS bought_by_1,
        MAX(user = 2 AND bought = 2) AS bought_by_2
FROM YourTable
GROUP BY item

See MySQL pivot table for more information about pivoting in MySQL. If the number of users is dynamic, so you can't hard-code them as I did, you'll need to write dynamic SQL. There are links to some web sites showing how to do this in the answers to that question.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612