0

I want to display total post where like is 3 or more;

I have 3 table

Table post:

id_post | title
1       | Ganteng
2       | serigala

Table User

id_user | username
1       | mantan
2       | otong
3       | pak_pol

Table vote:

id_vote | id_post | LIKE | id_user
1       | 1       | 1    | 2
2       | 1       | 1    | 1
3       | 1       | 1    | 3
4       | 2       | 1    | 1
5       | 2       | 1    | 2
6       | 2       | 1    | 3

Here is my query:

$sql="SELECT COUNT(v.id_post) as total_post
FROM vote v 
LEFT JOIN post p ON p.id_post=v.id_post
HAVING SUM(`like`) >= 3";

In my expectation, the value of $sql should be 2... But the return of value is 6

Any answer?

Many thanks

Andhika R.K.
  • 426
  • 1
  • 11
  • 30
  • Here is why your query does not work: `COUNT(v.id_post)` counts all post IDs that are not null, which is true for all post IDs, i.e. all records, i.e. 6. `SUM(like)`, adds up all like values, i.e. 6 x 1 = 6. This is >= 3, so you show the record count (6). What your query is mainly missing is a GROUP BY clause on post ID, so as to ask for votes *per* post ID. – Thorsten Kettner Jan 26 '15 at 09:02

2 Answers2

0

what you are looking for is Distinct Count. For Distinct, whichever is the right syntax in the SQL you are using, use that.

In response to Jens valid comment on Like being a keyword, I have used square brackets to qualify it. Use the appropriate syntax in your language to qualify field names that are possible keywords. 

$sql="SELECT COUNT(***DISTINCT*** p.id_post) as total_post
FROM post p 
LEFT JOIN vote v ON p.id_post=v.id_post
HAVING SUM([like]) >= 3";
Whirl Mind
  • 884
  • 1
  • 9
  • 18
  • still giving result = 3 – Andhika R.K. Jan 26 '15 at 07:45
  • Just noticed the mysql tag on your question (I had assumed SQL server, sorry). I think, in MySql, the Distinct word shouldn't have its own parentheses ? As in http://stackoverflow.com/questions/5737628/mysql-count-distinct: Edited my answer to remove the braces around the Distinct phrase. – Whirl Mind Jan 26 '15 at 07:50
  • I think you have changed the question to the other way round of the left join, starting the left join with vote unlike your earlier version where the left join started with post on the left. Those are two different questions, LoL. Neways, if you are starting a join with vote, for this, you do not need to use a left join, use an inner join. – Whirl Mind Jan 26 '15 at 08:08
0

There are two steps to do:

  1. Find all posts with more than two likes. (So group by post and see if it has more than two likes.)
  2. Count those posts.

Query:

select count(*)
from
(
  select id_post
  from vote
  group by id_post
  having count(*) >= 3
) well_liked_posts;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Oh, sorry, some dbms require this. Please see my edited answer. I've given the derived table the alias "votes". – Thorsten Kettner Jan 26 '15 at 08:06
  • Thanks for accepting. I'm changing the alias name, though, as it is not votes I am selecting from the vote table, but well liked posts. So I'm changing the alias name from "votes" to "well_liked_posts". – Thorsten Kettner Jan 26 '15 at 08:55