Problem with query execution (not in GROUP BY)

Question

I am using PHP and trying to execute a query in MySQL,

When I execute this query using let MySqlYog, I get the result, and everything seems to be in order.

Request:

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 try to execute a request from PHP:

I get this error:

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

If anyone has experienced this question, I will be glad to hear how to overcome it?

I need GROUP BY output in the following format:

The first day of the week in DATE format in the first column and counting events as the second column.

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

Answer 1

If Date should be the first start_time week found in the table, and not the first day of the week according to the calendar, then you can simply fill in start_time as follows:

 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 probably shouldn't group only WEEK(start_time) . Last year dates may have the same week number, and your request will group different weeks together. To fix this, you can simply 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 ; 

Answer 2

If you group by WEEK start_time, then this is what you should choose (instead of the date itself). Otherwise, how would you report bills? You want to show the counter every week, not every date.

You also have a semantic error in your where argument - you must use parentheses to explicitly set the priority order in your ANDs 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 request:

 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 ; 

It 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 I execute this request from MySqlYog (Windows MySql client), I get the desired result, problems arise when I execute this request 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 is the error I get 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 here is what it looks like in the 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()); }