CODEWITHSUNDEEP
ms-accessgroup-byaggregate-functions
rhavendc
rhavendc

Reputation: 1013

MS Access Query Error with Aggregate and Group By Functions

I just want to be enlightened with the issues I'm facing right now with a MS Access database. I can actually get results from a simple query statement but whenever I'm using Aggregate and/or Group By functions, I'm getting an error.

$rowno = 1;

$query = "SELECT COL1, MIN(COL2) AS time_in, MAX(COL2) AS time_out FROM TBL
        WHERE FORMAT(COL2, 'yyyy') = '2017' 
        GROUP BY COL1 
        ORDER BY COL2 DESC";

foreach ($dbh->query($query) as $row){
    echo $rowno . ") " . $row['COL1'] . " - " . $row['time_in'] . " - " . $row['time_out'] . "<br/>";
    $rowno++;
}

And it gives me:

Warning: Invalid argument supplied for foreach() in ...

My confusion is why the functions didn't work when actually the same query statement is working with another MS Access database? Is this something data or version problem? The statement is working when I removed the MIN(), MAX() and GROUP BY stuffs.

But anyway, here is a sample data that can be seen in the database I'm currently using:

COL1          COL2
 1      8/10/2017 4:53:31 PM
 1      8/10/2017 4:50:31 PM
 2      8/10/2017 4:43:31 PM
 2      8/10/2017 4:40:31 PM
 3      8/10/2017 4:33:31 PM
 3      8/10/2017 4:30:31 PM

Upvotes: 0

Views: 243

Answers (1)

Erik A
Erik A

Reputation: 32682

Your ORDER BY statement is not an aggregate, while you're using a totals query. This should work:

$query = "SELECT COL1, MIN(COL2) AS time_in, MAX(COL2) AS time_out FROM TBL
        WHERE FORMAT(COL2, 'yyyy') = '2017' 
        GROUP BY COL1 
        ORDER BY MIN(COL2) DESC";

If your order by is not an aggregate, conflicts can occur. Assume you had the following table:

COL1          COL2
 1      8/10/2017 4:53:31 PM
 1      8/10/2017 4:50:31 PM
 2      8/10/2017 4:59:31 PM
 2      8/10/2017 4:40:31 PM

If you just order this by COL2, Access wouldn't know if it should put the aggregate row for COL1: 1 before or after COL1: 2, since for COL1:2, COL2 is both higher (in row 3) and lower (in row 4) than for COL1: 1

Upvotes: 2

Related Questions