Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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? –  ypercube Jul 30 '12 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 '12 at 12:09
    
Sum per GroupTranscation? That should be ok but the other columns (ReceiptNo, TransactionNo, ActualCost, etc.) will probably show indeterminate results. –  ypercube Jul 30 '12 at 12:12

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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