0

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
3
  • Your query seems broken (e.g. it will display indeterminate results). What happens if you remove the GROUP BY? Or why do you need it, what is the query supposed to return? Jul 30, 2012 at 12:06
  • @ypercube i need it for the sum as it sums up the amount group weirdly it runs smoothly but no token displays and they are about 100,000 of them
    – Sitati
    Jul 30, 2012 at 12:09
  • Sum per GroupTranscation? That should be ok but the other columns (ReceiptNo, TransactionNo, ActualCost, etc.) will probably show indeterminate results. Jul 30, 2012 at 12:12

2 Answers 2

2

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.