1

I've been trying to write a query to select all records from a table where the sum of a column's values is between a variable range.

Here's what I came up with so far:

$result = mysql_query(' SELECT * FROM table WHERE SUM(column) BETWEEN $Range1 AND $Range2 ORDER BY RAND());

However when I try to loop through the above query with the mysql_fetch_object function it gives me a common error (The supplied argument is not a valid result).I've tried different ways of writing it but still come up short

So I tried the query using aliases you guys provided but still get the same error.

$result = mysql_query(' SELECT column1, SUM(column2) AS Total FROM table GROUP BY column1 HAVING Total BETWEEN $Range1 AND $Range11 ORDER BY RAND()');
Comrade_Slim
  • 13
  • 3
  • You can't use aggregate functions (such as `SUM()`) in WHERE clauses, and without a `GROUP BY` you are only getting the sum of that field for the entire table. – Uueerdo May 12 '15 at 20:09
  • 1
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 12 '15 at 20:11

1 Answers1

1

I'm not sure what the "ordering by rand" is needed for but your final query will look something like this:

SELECT *, SUM(column) AS `total`
FROM table 
GROUP BY someColumn
HAVING `total` BETWEEN $Range1 AND $Range2 
ORDER BY RAND()
;
Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • My Php knowledge is very very very rusty, your best bet is to add what you tried to the question and see if anyone sees something. – Uueerdo May 13 '15 at 20:13