MySQL Statement not displaying some values

Question

I have a MySQL Statement that runs perfectly but does not display some of the values from the table that it is supposed to display. My table has got a column called 'token' which has values that are generated from another table. The values are numeric and 20 characters long so its something like '57683913499284962398' which I transfer to the working table.

I would like to be able to select the token which is already generated but on running my query it is the only column that is not displayed here is my query:

SELECT `Group Transaction No`,`Receipt No`, `Transaction No`, `Actual Cost`,
`CErr`,`Resource Units`, `Meter Serial No`, `Token`, `Transaction Timestamp`,
ROUND(SUM(`Transaction Amount`),0) as Amount FROM `efulusi_acc`
GROUP BY `Group Transaction No`
ORDER BY `efulusi_acc`.`Token` limit 5

Answer 1

Your query is just plain invalid, as you have included values in the column list that are not part of the GROUP BY. MySQL lets you do this (unfortunately), but the results will be indeterminate. Consider setting sql_mode=TRADITIONAL in my.cnf (and SET GLOBAL sql_mode=TRADITIONAL)

A valid query would like like this, though I'm not sure it will get you the results that you want.

SELECT `Group Transaction No`,`Receipt No`, `Transaction No`, `Actual Cost`,
`CErr`,`Resource Units`, `Meter Serial No`, `Token`, `Transaction Timestamp`,
ROUND(SUM(`Transaction Amount`),0) as Amount FROM `efulusi_acc`
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 8 
LIMIT 5

Also, it's a really bad idea to include spaces in your column names. It's just plain difficult to work with and can cause a lot of edge case bugs.

Answer 2

I was able to find a solution to my problem by adding MAX instead of SUM to my 'token' column and viola every token was correct thanks a lot for all the help i hope this helps someone else with the same problem.

SELECT Group Transaction No, Transaction No, Actual Cost, CErr,Resource Units, Meter Serial No, CONVERT(MAX(Token) USING latin1), Transaction Timestamp, ROUND(SUM(Transaction Amount),0) as Amount FROM efulusi_acc GROUP BY Group Transaction No ORDER BY efulusi_acc.Token