Help With a Very Complicated Query

[evenstar7139]

This query is for a page on my site that lets people look and see how their dog has done over the course of its show career. Each show will be grouped together and you'll be able to see what placements your dog won in each show as well as the total ch points, breed points, and all breed points won at the show. Every placement a dog achieves in a dog show is stored in a separate row and the ch points, breed points, and/or all breed points it won with that win are in that row.

This query will initially search the show_results table in my MySQL database for rows in which dog_id is the same as the value presented in the WHERE clause.

And arbitrary visualization:
SELECT * FROM show_results WHERE dog_id='234233'

Then, after having located all the rows where dog_id matches the value the WHERE clause asks for, it groups them together on the value show_id. So, for instance, all rows with a show_id of 5 would be put together.

This makes it so there is one result per show_id.

And in so doing this, the following needs to happen:
The values of ch_points_won are added together to form a single value which will come out as the value of ch_points_won.
The same thing is done for breed_points_won.
The same thing is done for all_breed_points_won.
Every value that is found in placement is put into a list.

So, like, if it finds five rows with a show_id of 7, it will add up the values of breed_points_won and spit it out as a single value. If those values were 5, 6, 7, 8, and 9, the value it spit out for breed_points_won would be 35. It would do the same for ch_points_won and all_breed_points_won.

And for every value it has in placement (they will be alphaneumeric) it combines them into one value separated by spaces. So, like, if there were five rows, and they had as their value for placement, respectively: WD, BOW, BOB, GRP1, BIS. Then it would be put together in a string like this "WD BOW BOB GRP1 BIS" and that would be output as the value of placement.

The primary key on this table is row_id, if you need to know. It's int and auto increment.

[Phill Pafford]

Try something like this

SELECT *, /* you might need to remove the *, as well */
SUM(ch_points_won) AS ch_points_won,
SUM(breed_points_won) AS breed_points_won,
SUM(all_breed_points_won) AS all_breed_points_won
FROM show_results
WHERE dog_id='234233' /* This finds all the dog_id */
AND show_id = '5' /* This will filter it to a specific show */
GROUP BY show_id DESC /* This will group by the show_id, this is also needed for the SUM command */

[evenstar7139]

Mmm very helpful.

How could I also make it take every value it finds for placement and put it into a comma separated list (a string like this: "BIS, GRP1, BOB")

It also has to be in a certain order. This is the order:

BIS, GRP1, GRP2, GRP3, GRP4, BOB, BOS, BOW, WD, WB, RWD, RWB


If I get all this down, then on my virtual dog game, you'll be able to search your dog's show records and see every placement and how much of each of the three kinds of points it won in each show.

e.g.

Angel Paws Kennel Club Show - Dec/15/2008

Placements: GRP3, BOB, BOW, WD
Total Ch Points Won: 3
Total Breed Points Won: 17
Total All Breed Points Won: 187

The show name and run date will be found with a join. All I need is the show id to pull up this info :)

[Phill Pafford]

Well I can get the order of it but you might need to use your program to parse out the return into a string.

Here is the query


SELECT placements AS pl
FROM show_results
ORDER BY
pl='BIS' DESC,
pl='GRP1' DESC,
pl='GRP2' DESC,
pl='GRP3' DESC,
pl='GRP4' DESC,
pl='BOB' DESC,
pl='BOS' DESC,
pl='BOW' DESC,
pl='WD' DESC,
pl='WB' DESC,
pl='RWD' DESC,
pl='RWB' DESC