CODEWITHSUNDEEP
phpmysqlpdo
Brian Coolidge
Brian Coolidge

Reputation: 4649

Mysql Count number of Rows Depending on Date

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. :)

Upvotes: 2

Views: 2880

Answers (3)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58481

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.

Upvotes: 5

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

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";

Upvotes: 1

alwaysLearn
alwaysLearn

Reputation: 6950

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";

Upvotes: 1

Related Questions