2

Well, I don't know what title it should be nor how can I explain it, but I'll do my best to explain my problem.

I have this table consists of certain data that focuses on date_participated and fblike_point.

enter image description here

Well, my desired output should be

2013-03-20 1

2013-03-19 3

So basically, each date will count the number of fblike_point.

On 2013-03-19 it has total of 3 fblike_point and on 2013-03-20 it has a total of 1 fblike_point

and my problem is, I'm outputting not my desired results, I'm getting a result of 2013-03-19 4

It stays in 1 date and counts the number of fblike_point

Here is my code for that.

$query = "SELECT DISTINCT date_participated, COUNT(fblike_point) as fblike FROM event_raffles WHERE ticket_id='". $ticket_id ."' AND event_table='". $event_table ."' AND status='1' ORDER BY date_participated DESC";
$result = db_query($query);

foreach ($result as $values) {
    echo $values->date_participated . " " . $values->fblike . "<br>";
}

Any solutions for this stuff? Any help would be appreciated. :)

Improve this question
1
  • use mysql_num_rows() function to count number of rows Commented Mar 20, 2013 at 6:53

3 Answers 3

Reset to default
5

You should alter your query to use a GROUP BY instead of the DISTINCT

SELECT date_participated, COUNT(fblike_point) as fblike 
FROM event_raffles
GROUP BY date_participated

The way you've used DISTINCT is to get all unique dates and for each result, add the amount of records returned in the entire resultset.

Using a GROUP BY allows you to use any aggregate function available on that group.

Edit

As been mentioned by @ysth, instead of using COUNT you might want to use SUM depending on the possible values that fblike_pointcan have.

Using SUM would actually be the more sensible course of action.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

or possibly SUM(fblike_point), depending what other values than 1 it might have and what should result in those cases
@ysth - Your are right, thanks. I have edited the answer to add the warning.
err, if fblike_point is null or 1, both will return the same; if it is 0, that row won't be counted by sum but will be counted by count
1

Replace 

$query = "SELECT DISTINCT date_participated, COUNT(fblike_point) as fblike FROM event_raffles WHERE ticket_id='". $ticket_id ."' AND event_table='". $event_table ."' AND status='1' ORDER BY date_participated DESC";

with

$query = "SELECT  date_participated, COUNT(fblike_point) as fblike FROM event_raffles WHERE ticket_id='". $ticket_id ."' AND event_table='". $event_table ."' AND status='1' GROUP BY date_participated ORDER BY date_participated DESC";
Improve this answer

1 Comment

You need to use group by here
1

use below query by removing DISTINCT and replacing ORDER BY to GROUP BY

$query = "SELECT date_participated, COUNT(fblike_point) as fblike FROM event_raffles WHERE ticket_id='". $ticket_id ."' AND event_table='". $event_table ."' 
AND status='1' GROUP BY date_participated DESC";
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.