List:General Discussion« Previous MessageNext Message »
From:Peter Lauri Date:April 12 2006 6:57am
Subject:RE: [SOLVED] Making result rows to one row
View as plain text  
Yes, you are correct. I tried it but I got some errors. I simplified my
query and tried it, and it worked. Then I added the more complicated parts
after that, and it works VERY good.

This is what makes life worth living :)

-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Tuesday, April 11, 2006 9:55 PM
To: Peter Lauri
Cc: mysql@stripped
Subject: Re: Making result rows to one row

Peter,

> Peter Brawley said:
>
> SELECT 
>   ..., 
>   GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS '  1  2  3  4  5  6  7
> 8  9'
> FROM  tour_player_score tps
> INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id
> WHERE tour_player_id=175
> GROUP BY tsh.id
>
> This worked, however, it gives me all results in one cell. I would like to
> have them sorted in one cell per strokes. Is that possible? 
Sure, that's what the pivot table example at 
http://www.artfulsoftware.com/queries.php#36 does. Your query would be 
something like...

SELECT player_id, 
MAX( IF(hole=1,strokes,'') ) AS 'Hole 1'
MAX( IF(hole=2,strokes,'') ) AS 'Hole 22, 
...
FROM tbl
GROUP BY player_id ...


PB


> The reason for
> this is that I have to use each hole as ORDER criteria. If the SUM is
equal,
> then I have to check the sum of the last nine holes. If they are the same,
> then I have to check the result of hole 18, 17, 16 etc.
>
> This is my exact query as is now:
>
> SELECT tps.tour_player_id, GROUP_CONCAT(LPAD(strokes,2,' ') SEPARATOR '-')
> AS hole_scores, sum(tps.strokes) AS 'score' FROM tour_player_score tps
INNER
> JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY
> tps.tour_player_id ORDER BY score;
>
> Best regards,
> Peter Lauri
>
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006

Thread
Making result rows to one rowPeter Lauri10 Apr
RE: Making result rows to one rowRobert DiFalco10 Apr
  • RE: Making result rows to one rowPeter Lauri11 Apr
Re: Making result rows to one rowPeter Brawley10 Apr
  • RE: Making result rows to one rowPeter Lauri11 Apr
Re: Making result rows to one rowPeter Brawley11 Apr
  • RE: [SOLVED] Making result rows to one rowPeter Lauri12 Apr