CODEWITHSUNDEEP
phpmysqlaveragegreatest-n-per-group
halk habi
halk habi

Reputation: 11

Calculate average for specific rows in MySQL

I have the following two tables:

content:

| id | name  | created_by |
+----+-------+------------+
| 1  | test  |     me     |
| 2  | test2 |     me     |
| 3  | test3 |     you    |
| 4  | test4 |     me     |

like_dislike:

| id | content_id | like | dislike |
+----+------------+------+---------+
| 1  |     1      |  1   |    0    |
| 2  |     2      |  1   |    0    |
| 3  |     2      |  0   |    0    |

I need to calculate the average likes of the last 2 content rows where created_by = me.

So for example, the last two content_ids are 2 and 4.

The like_dislike table has records for content_id 2, with a row where like is 1 and a row where like is 0, so the average is 1/2.

I have tried the following query but it will not work:

SELECT ((SELECT COUNT(*) 
   FROM `like_dislike` 
   WHERE `like`='1' AND `content_id`= pi.id) / (COUNT(*))
FROM content AS pi 
WHERE pi.create_by = 'me' 
ORDER BY pi.id DESC
LIMIT 2

Upvotes: 1

Views: 1923

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you only want this information for one user at a time, then you don't need to do fancy things with self joins or variables. You can just use limit in a subquery:

select avg(like)
from like_dislike ld join
     (select c.*
      from content c
      where c.create_by = 'me'
      order by id
      limit 2
     ) c2
     on ld.content_id = c2.id;

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

SELECT x.* 
     , AVG(liked) 
  FROM content x 
  JOIN content y
    ON y.created_by = x.created_by 
   AND y.id >= x.id 
  JOIN like_dislike z
    ON z.content_id = x.id
 WHERE x.created_by = 'me' 
 GROUP BY x.id 
HAVING COUNT(DISTINCT y.id) <= 2;

or something like that

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16691

It seems like what you're trying to do here also, even though you might not see it, is getting the greatest-n-per-group. See this article for more information.

So, what I would do is start by getting the latest two content rows that are created by you, like so:

SELECT c.*
FROM content c
WHERE(
  SELECT COUNT(*)
  FROM content co
  WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me';

The next part I will step through a little slower because it is kind of tricky. You can join the above with the like_dislike table on the condition that the content_id matches, so you can view the total likes for each of your last two posts:

SELECT c.*, ld.*
FROM content c
JOIN like_dislike ld ON ld.content_id = c.id
WHERE(
  SELECT COUNT(*)
  FROM content co
  WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me';

Once you have that, you can use the AVG() function to get the average likes, and group by each content_id, like this:

SELECT c.id, AVG(ld.liked) AS averageLikes
FROM content c
JOIN like_dislike ld ON ld.content_id = c.id
WHERE(
  SELECT COUNT(*)
  FROM content co
  WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me'
GROUP BY c.id;

One important thing to note here: This will only show the post that has activity associated with it. In other words, content_id 4 will not appear. If you need to see it, you can do a LEFT JOIN, and check if the average is null and replace it with 0 if it is, like this:

SELECT c.id, IFNULL(AVG(ld.liked), 0) AS averageLikes
FROM content c
LEFT JOIN like_dislike ld ON ld.content_id = c.id
WHERE(
  SELECT COUNT(*)
  FROM content co
  WHERE co.created_by = c.created_by AND co.id >= c.id) <= 2
AND c.created_by = 'me'
GROUP BY c.id;

And here is your SQL Fiddle.

ALSO NOTE like is a reserved word in MySQL, so I changed the two columns in the fiddle to be liked and disliked. I recommend you do the same.

Upvotes: 0

Related Questions