CODEWITHSUNDEEP
phpmysql
user2002220
user2002220

Reputation: 133

How to combine multiples sum and dates from mysql

Im trying to generate data for a graph but everytime i attempt to do it it basically just gives me 1 sum. For the graph i need the date and the value.

This is my current code:

$chart_data = "";
    $earnings_query = mysql_query("SELECT SUM(R.rate) as ratess,R.date FROM reports R WHERE R.pid = '$publisher_id' AND R.status = '2'");
    if (mysql_num_rows($earnings_query) > 0)
    {

        while ($row = mysql_fetch_array($earnings_query))
        {
            $date = date("m/d/Y",strtotime($row['date']));
            $chart_data.= '{"date": "'.$date.'", "value": '.$earnings_total['tot'].'},';
        }
    }

    echo $chart_data;

the output is {"date": "12/31/1969", "value": }, but i should be getting daily data all the way back from June 6.

Thanks to all the comments below, the code below solves my problem.

$earnings_query = mysql_query("SELECT SUM(R.rate) as ratess, R.date FROM reports R WHERE R.pid = '$publisher_id' AND R.status = '2' GROUP BY date(R.date)"); if (mysql_num_rows($earnings_query) > 0) {

while ($row = mysql_fetch_array($earnings_query))
{
    $date = date("m/d/Y",strtotime($row['date']));
    $chart_data.= '{"date": "'.$date.'", "value": '.$row['ratess'].'},';
}

}

echo $chart_data;

Upvotes: 1

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need a group by clause:

SELECT SUM(R.rate) as ratess, R.date
FROM reports R
WHERE R.pid = '$publisher_id' AND R.status = '2'
GROUP BY R.date;

Without the group by, your query is an aggregation query that runs over the entire set of data. The result (without a group by) is always going to be one row.

If your column called date has a time component, then you might want to use the date() function:

SELECT SUM(R.rate) as ratess, date(R.date) as date
FROM reports R
WHERE R.pid = '$publisher_id' AND R.status = '2'
GROUP BY date(R.date);

Upvotes: 1

css
css

Reputation: 944

Add a GROUP BY date to your SQL command.

SELECT SUM(R.rate) as ratess, R.date
FROM reports R
WHERE R.pid = '$publisher_id' AND R.status = '2'
GROUP BY R.date

This will sum for each date in the range, whereas you were summing for all dates and then selecting the first date. If the date field isn't just a date, but also includes time information, you'll want to use DATE(R.date) instead.

Upvotes: 0

Related Questions