Calculating table standings

Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals

SUM value like 10,23,15,10

Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil

Re: SUM value like 10,23,15,10

If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com

Re: SUM value like 10,23,15,10

Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.bewrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do

Re: SUM value like 10,23,15,10

Then you're pretty much on your own, I'm afraid. Not a very good way to store data :-) You could maybe build a stored procedure, or do it in the app; but it's gonna be code either way. On Tue, Aug 24, 2010 at 3:58 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Yeah these values are

Re: SUM value like 10,23,15,10

The application is still being developed, so I will probably look at storing it in separate tables so that it can easily be computed. On Tue, Aug 24, 2010 at 3:01 PM, Johan De Meersman vegiv...@tuxera.bewrote: Then you're pretty much on your own, I'm afraid. Not a very good way to store data

Re: SUM value like 10,23,15,10

The proper way to do this would indeed be a separate table that has (itemID, property, value) or something like that. On Tue, Aug 24, 2010 at 4:04 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: The application is still being developed, so I will probably look at storing it in

RE: Calculating table standings

I think your match table has all the information necessary to display the results you want. Since each record contains data for two teams (home and away), you'd probably need to select each separately and union the results together before summarizing. Your query might look something like the

Re: Calculating table standings

Travis Thanks for the response. Do you recommend I store the data in this way ? Or should I look at storing in a separate leagues table, why by I list all the data in the separate columns for each round and then just compute a fairly basic query ? What is the recommend way ? Cheers Neil On

Complex Select Query

Hi; I have the following query: select * from spreadsheets s join products p on p.Item=s.Item join categories c on p.Category=c.ID where s.Client=%s order by p.category, c.parent; mysql describe products; +-+--+--+-+-++ | Field | Type

Re: Complex Select Query

What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former category, then move on to the next category, etc. How do I do this? It's a tree. See

Re: Complex Select Query

On Tue, Aug 24, 2010 at 1:43 PM, Peter Brawley peter.braw...@earthlink.netwrote: What I'm trying to accomplish is to order the results such that after stacking the data for all results for a certain category, that the next results to be stacked should be those whose parent = the former

BLOB data gets encoded as utf8!

Hello everyone! I am using an MFC unicode project that uses ODBC to access a MySQL 5.1.50 database via the MySQL ODBC 5.1.6 driver. One of the tables contains two LONGBLOB columns, and the table default charset is utf-8 (since the application is unicode). However, when inserting into the

BLOB data gets encoded as utf8! (First post incomplete)

Hello everyone! I am using an MFC unicode project that uses ODBC to access a MySQL 5.1.50 database via the MySQL ODBC 5.1.6 driver. character_set_connection is set to utf8 (Which I believe is the default for the driver) One of the tables contains two LONGBLOB columns, and the table default

Always corrupted after restart server

Hi List, I have a very big size MyISAM table. For some reason I need to restart the server periodically. But After restarting the server, the table always get corrupt, and always need to run myisamchk. Don't know what cause the problem. But it will be very helpful if somebody can give me some

How many pager command within mysql command line client?

Hi. For example, entering mysql command line client, mysql pager more ( or pager md5sum and so on.) I want to know how many command the 'pager' follows? Any reply will be big appreciated.