Problem to execute query (isn't in GROUP BY)

I'm using PHP and trying to execute quer开发者_如何学编程y on MySQL,

When I'm executing this query using let's say MySqlYog I'm getting the result and everything is seems to be ok.

Query:

SELECT 
  start_time AS `Date`,
  COUNT(1) AS `Count` 
FROM
  offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY
AND application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT'
GROUP BY (WEEK(start_time))
ORDER BY `Date` ASC ;

But the problem is when I'm trying to execute the query from the PHP:

I'm getting this error:

Invalid query: 'nolio_db.offline_execution_jobs.start_time' isn't in GROUP BY

If anyone experienced with this issue I'll be glad to hear how it possible to overcome it?

I need the output of the GROUP BY in the following format:

First day of the week in DATE format on the first column and the count of the events as the second column.

2011-01-09 03:28:54 | 38

---

If you're grouping by the WEEK of start_time, then thats what you should be selecting (instead of the date itself). Otherwise, how would you report the counts? You want to show the count for each week, not each date.

You also have a semantic error in your where clause - you should use parenthesis to explicitly set the order of precedence on your AND and ORs.

SELECT WEEK(start_time) AS Week, COUNT(1) AS Count 
FROM offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY 
AND (application_name LIKE 'SPLAT-ROLLING' 
OR application_name LIKE 'SPLAT' )
GROUP BY WEEK(start_time)
ORDER BY WEEK(start_time) ASC ;

This query:

SELECT WEEK(start_time) AS Week, COUNT(1) AS Count 
FROM offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY 
AND (application_name LIKE 'SPLAT-ROLLING' 
OR application_name LIKE 'SPLAT' )
GROUP BY WEEK(start_time)
ORDER BY WEEK(start_time) ASC ;

Returns the following result:

1 21
2 50
3 15

But I need something like this:

2011-01-04 08:05:24  21
2011-01-09 03:28:54  8
2011-01-16 06:08:18  11
2011-01-23 06:06:50  32

And when executing this query from MySqlYog (MySql windows client), I'm getting the desired result, the problems occurs when I'm executing this query from php code:

SELECT start_time AS WEEK, COUNT(1) AS COUNT 
FROM offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY 
AND (application_name LIKE 'SPLAT-ROLLING' 
OR application_name LIKE 'SPLAT' )
GROUP BY WEEK(start_time)
ORDER BY WEEK(start_time) ASC ;

Here's the error I'm getting from php:

Invalid query: 'nolio_db.offline_execution_jobs.start_time' isn't in GROUP BY

Whole query: SELECT start_time AS Date, COUNT(1) AS Count FROM offline_execution_jobs WHERE start_time >= NOW() - INTERVAL 250 DAY AND (application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT' ) GROUP BY WEEK(start_time) ORDER BY WEEK(start_time) ASC ;

And this is how it looks int he code:

$query =    "SELECT start_time AS Date, COUNT(1) AS Count 
                    FROM offline_execution_jobs 
                    WHERE start_time >= NOW() - INTERVAL 250 DAY 
                    AND (application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT' )
                    GROUP BY WEEK(start_time)
                    ORDER BY WEEK(start_time) ASC ;";

    //echo "<br><br>$query<br><br>";

    // Create connection to DB                              
    $conn = mysql_connect($db_host, $db_user, $dp_pass);

    if (!$conn) 
    {   
        echo "<br/>Can't connect: $db_host";
        die('Could not connect: ' . mysql_error());
    }

---

If Date should be the first start_time value of the week found in the table, rather than the first day of the week according to the calendar, then you could simply aggregate start_time like this:

SELECT 
  MIN(start_time) AS `Date`,
  COUNT(1) AS `Count` 
FROM
  offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY
AND application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT'
GROUP BY (WEEK(start_time))
ORDER BY `Date` ASC ;

By the way, you should probably not grouping by WEEK(start_time) alone. Last year dates may have the same week number, and your query will thus group different weeks together. To fix that, you can just add YEAR(start_time) to the GROUP BY list:

SELECT 
  MIN(start_time) AS `Date`,
  COUNT(1) AS `Count` 
FROM
  offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY
AND application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT'
GROUP BY YEAR(start_time), WEEK(start_time)
ORDER BY `Date` ASC ;