Can't seem to order by SUM in PHP query

PHP
1

Hi, I’ve got a little report set up which basically adds up payments by customers from a payments table and then outputs the customer name followed by the total they’ve paid.

It works except that I can’t get it to order by the customer with the most payments at the top, and then working downwards.

The MySQL I’m using is:

"SELECT Name, SUM(Amount) AS SumOfAmount FROM Transactions WHERE Name LIKE '$clientname' AND PaymentInOut LIKE 'IN' GROUP BY Name ORDER BY SumOfAmount DESC";

I read in various places that I had to use GROUP BY in order to get a query to sort by a SUM of something, but it doesn’t seem to work. What happens instead is bizarre- it orders it by oldest customers first. Any ideas how I can get it so that the most “valuable” customer (who’s paid the most) is at the top, and then in order of decreasing “value”?

2

That’s strange, because your query looks absolutely correct.
What results do you have with it? How they’re sorted?
I’m just tried to run similar query on my own table:

SELECT user_id, SUM(summ) as total FROM `orders` 
WHERE user_id > 100 GROUP BY user_id ORDER BY total DESC

and it works

3

Basically it lists Client name - Total Paid, for each client, but it does it in order of when the client was added to the system. So for example, the very first client that was on the system (and who made the very first payment) is first, followed by the 2nd, then 3rd, and so on, so that at the bottom are the most recent clients.

4

Just putting the query problem aside for a moment, you’ve probably got an SQL Injection vulnerability there if $clientname is a bit of user submitted data and it’s not being sanitized or escaped. If you’re using the old mysql_* extension if you’re not already aware, you need to be aware that as of version 5.5 of php it has been depreceated and as of version 7 of PHP it has been removed. You should really be using prepared statements when dealing with user-submitted data.

When dealing with multiple conditions in the WHERE clauses, it’s better to enclose each one in brackets

5

Did you try to run this query in phpMyAdmin or something like that?
I mean try to test it in different environment

6

Yes please try that. Apart from the two LIKE’s that should be = your query looks ok.

7

Thanks all. Well that’s interesting- in phpMyAdmin it runs perfectly, although obviously I had to take out the ‘$clientname’ clause.

I then tried the same thing in the script (missing out $clientname):

SELECT Name, Category, SUM(Amount) AS SumOfAmount FROM Transactions WHERE PaymentInOut LIKE 'IN' GROUP BY Name ORDER BY SumOfAmount DESC

It outputs the sums of values correctly, i.e number 1 client at top, then ion order downwards. unfortunately in the Client Name column it just lists the name of the first client in the table, over and over.

So:

Name of oldest Client - total value of best client
name of oldest Client - total value of 2nd best client

etc.

So the value column works fine, but I’d like the correct client name next to it!

8

try to group by id instead of name

9

Unfortunately that shows no listings at all (checked and ID is indeed the name of the ID index)

SELECT ID, Name, Category, SUM(Amount) AS SumOfAmount FROM Transactions WHERE PaymentInOut LIKE 'IN' GROUP BY ID ORDER BY SumOfAmount DESC
  • Just sorted it- added in a new variable for pulling the name out in the while loop, and now got it working. i.e
while {etc ...
$client = $row['Name'];

Thanks guys for all the help.

10

Why are you including specifying a specific client in the query at all? There is no need to put the query in a loop - just amend the query so that it returns all of the results at once.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.