query has stopped working...

[abba]

This query used to work but recently stopped working. Presumably with the an upgrade. It now returns 0 rows.

SELECT playername, sum(totalhands), sum(vpiphands), sum(pfrhands)
FROM compiledplayerresults
JOIN compiledresults
ON compiledplayerresults.compiledplayerresults_id = compiledresults.compiledplayerresults_id
JOIN players
ON compiledresults.player_id = players.player_id
GROUP BY playername
HAVING sum(totalhands) >= 100

Do you know how I can rewrite the query to make it work with the updated HM?

[leander]

I guess this is what you are looking for:

SELECT 
  playername, SUM(totalhands), SUM(vpiphands), SUM(pfrhands)
FROM 
  compiledplayerresults_month
  JOIN compiledresults_month 
  	ON compiledplayerresults_month.compiledplayerresults_id = compiledresults_month.compiledplayerresults_id
  JOIN players
  	ON compiledresults_month.player_id = players.player_id
WHERE compiledresults_month.totalplayedhands >= 100
GROUP BY playername

[Rvg72]

yes thanks, add a _month to every compiled results table name and it will work (and be way faster)

Roy

[_Hannes_]

Does this _month postfix limit the hands/results to the last month or why did the developers call it _month?

[Rvg72]

we eliminated the use of the non _month tables due to performance reasons. The _month tables work the same way as before though and stats correspond to a single month (not just last month)

[KittyLiquor]

1) You indicated I can "DROP" the following tables...

compiledplayerresults
compiledplayerresultspositionaction
compiledresults
postflopactions

and leave the cooresponding *_month tables. Correct? (I'll backup first!)

2) In pgAdminIII when I click the spreadsheet button (view the data in selected object) it, by default, gets ALL rows. Any way to permanently change the default? (off topic question )

3) compiledplayerresults_month has the most current up-to-date stats with 1 row per gametype & month & number of opponents?
4) and I need to look in compiledresults_month to match player_id with the correct compiledplayerresults_id(s)?

5) I have a lot of players with 0 hands playerd. I assume they are very old SNG opponents. Can I delete them?
0 hands = Select *from playerhandscashkeycolumns where player_id = $row_Players['player_id']
(Obviously the compiled tables will be a faster way to determine 0 hands, once I figure out how the compiled tables work. lol)

Thanks!

----------------------Kitty