Thread: help with a query
i have data bast that represents basketball teams. and i'm trying to find one team that has not won yet but has gain that most points during their games from the other team who have not won yet.
i wrote a query that is checking if a team has not won at all. but i'm trying to figure out how to count the points because every team play either at home or away.
thank you for you help.
here is the query the i have wrote so far:
select team.id, nickname,color
from team,points,game
where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = team.tid and (hscore - vscore )< 0 )
having
from team,points,game
where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = team.tid and (hscore - vscore )< 0 )
having
Due to time constraints, I do not directly answer general PostgreSQL
questions. For assistance, please join the appropriate mailing list and
post your question:
https://www.postgresql.org/community
You can also try the #postgresql IRC channel on irc.freenode.net. See
the PostgreSQL FAQ for more information.
---------------------------------------------------------------------------
On Sat, Nov 13, 2021 at 11:13:25AM +0200, yarden tamam wrote:
> i have data bast that represents basketball teams. and i'm trying to find one
> team that has not won yet but has gain that most points during their games from
> the other team who have not won yet.
>
> i wrote a query that is checking if a team has not won at all. but i'm trying
> to figure out how to count the points because every team play either at home or
> away.
>
> thank you for you help.
>
> here is the query the i have wrote so far:
> select team.id, nickname,color
> from team,points,game
> where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid =
> team.tid and (hscore - vscore )< 0 )
> having
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
On Mon, Nov 15, 2021 at 09:30:03AM -0500, Bruce Momjian wrote: > > Due to time constraints, I do not directly answer general PostgreSQL > questions. For assistance, please join the appropriate mailing list and > post your question: > > https://www.postgresql.org/community > > You can also try the #postgresql IRC channel on irc.freenode.net. See > the PostgreSQL FAQ for more information. Sorry, my apologies for saying the above. You are asking in the right place, though the question is so unclear and with no specific Postgres aspect, you might be better in an SQL forum. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Yarden:
On Mon, 15 Nov 2021 at 10:30, yarden tamam <tamamyarden@gmail.com> wrote:
> i have data bast that represents basketball teams. and i'm trying to find one team that has not won yet but has gain
thatmost points during their games from the other team who have not won yet.
> i wrote a query that is checking if a team has not won at all. but i'm trying to figure out how to count the points
becauseevery team play either at home or away.
> thank you for you help.
> here is the query the i have wrote so far:
> select team.id, nickname,color
> from team,points,game
> where (team.tid = game.vtid and (vscore - hscore ) < 0) or (game.htid = team.tid and (hscore - vscore )< 0 )
> having
This has the faint smell of homework assignment, but anyway, decompose
the problem a bit and build from it, if I understand your logic you
can do more or less...
with home_games_total as ( select htid as tid, sum(hscore) as
tscore, sum (vscore as oscore), max(hscore>vscore) as won_any )
This captures total result for home games for the team, for the other
team, and wether it has won any match ( using the fact that true>false
in max, there are better ways but this is easy to type for an example
). Then you can add....
with visitor_games_total as ( select vtid as tid, sum(vscore) as
tscore, sum (hscore as oscore), max(vscore>hscore) as won_any )
to capture visitor teams stats, then you just have to combine them with
with all_games_totals as (select * from home games total union all
select * from visitor_games_total )
note each team will have play twice, one for the home team, one for
the visitor in the first two sum, and the last subquery will have two
rows per team, one for home games, one for visitor. Now just add them
all to get one row per team
with team_result as (select tid, sum(tscore), sum(oscore),
max(won_any) from all_games_total group by tid)
and there you just have to search for wat you want:
select * from team_result where not won_any order by tscore desc
limit 1 -- ( or use tscore-oscore, if you want point diffs )
From this you can probably optimize, but if your tables are not too
big doing it that way is easy for maintenance. And even if you have
all playing history for NBA if you limit first two queries to a season
they will not spit too many results. Also the first two queries plus
the third can be done in a single scan by capturing stats for home and
visitor in a single round, then sum it with itself with columns
swapped.
Also note, this is totally untested code and probably chock full of
typos and errors.
Francisco Olarte.