Click here to Skip to main content
15,561,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the next query
SQL
<pre>query = '''SELECT players.playerID, lastName, SUM(points) AS AllPoints, deathDate
    FROM players
    
    INNER JOIN players_teams 
    ON players.playerID = players_teams.playerID  
    
    GROUP BY players.playerID, lastName, deathDate  
    
    HAVING SUM(points) > 30000 AND deathDate = "0000-00-00"
    '''
puntos = pd.read_sql(query,db)
puntos


the table is:
playerID	lastName	AllPoints	deathDate
0	abdulka01	Abdul-Jabbar	38387.0	0000-00-00
1	ervinju01	Erving	30026.0	0000-00-00
2	jordami01	Jordan	32292.0	0000-00-00
3	malonka01	Malone	36928.0	0000-00-00


and I want to add the filter that give the players with more than 30K points and no deathDate with some award

What I have tried:

I try this

SQL
<pre>query = '''SELECT players.playerID, lastName, SUM(points) AS AllPoints, award, deathDate
    
    FROM players
    
    INNER JOIN players_teams 
    ON players.playerID = players_teams.playerID  
    
    INNER JOIN awards_players
    ON players.playerID = awards_players.playerID  

    GROUP BY players.playerID, lastName, award , deathDate
    
    HAVING SUM(points) > 30000 AND award IS NOT NULL and deathDate = "0000-00-00"
    
    '''

puntos2 = pd.read_sql(query,db)
puntos2



but this give a mix and repeat the players because for example if one player won two award the query make two time the SUM() of him points. I need the filter with a correct sum

i have this query ( it is incorrect )

	playerID	lastName	AllPoints	award	deathDate
0	abdulka01	Abdul-Jabbar	191935.0	All-Defensive First Team	0000-00-00
1	abdulka01	Abdul-Jabbar	230322.0	All-Defensive Second Team	0000-00-00
2	abdulka01	Abdul-Jabbar	383870.0	All-NBA First Team	0000-00-00
3	abdulka01	Abdul-Jabbar	191935.0	All-NBA Second Team	0000-00-00
4	abdulka01	Abdul-Jabbar	76774.0	Finals MVP	0000-00-00
...	...	...	...	...	...
228	wilkido01	Wilkins	106672.0	All-NBA Second Team	0000-00-00
229	wilkido01	Wilkins	53336.0	All-NBA Third Team	0000-00-00
230	willibu01	Williams	33568.0	All-Defensive First Team	0000-00-00
231	willibu01	Williams	33568.0	All-Defensive Second Team	0000-00-00
232	worthja01	Worthy	32640.0	All-NBA Third Team	0000-00-00
Posted
Updated 6-Nov-22 22:49pm
v2
Comments
Member 15627495 5-Nov-22 4:03am    
to avoid redundant line use 'distinct'.

and to fix the inner join ( which collapse table ), you have another way to achieve the sub-request
 select * from tbl where criteria IN (select id, ..... from table2 where criteria2 = .... ) 

it's equal to JOIN use , but more readable.
David Vazquez Bande 5-Nov-22 4:27am    
sorry I don't understand the your query or your resolution

1 solution

I dont understand very well what the query means, but try
SQL
query = '''SELECT players.playerID, lastName, SUM(points) AS AllPoints, deathDate
    FROM players
    
    INNER JOIN players_teams 
    ON players.playerID = players_teams.playerID  

    WHERE award IS NOT NULL
    
    GROUP BY players.playerID, lastName, deathDate  
    
    HAVING SUM(points) > 30000 AND deathDate = "0000-00-00"
    '''
puntos = pd.read_sql(query,db)
puntos
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900