[SOLVED] SUM output into table

[nikkicade]

Hi

 

I have a table with reults and points in. I want to add all the points and then insert the total in a column called TotalPts. I can get the total worked out using the following:-

 

Select Username, sum(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) from game20sep group by Username;

 

This displays the username and TotalPts for each user but rather than me editing the TotalPts column for each user is there a simple way of inserting the TotalPts for each user?

 

I have tried:-

 

Update game20sep set TotalPts=sum(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) group by Username;

 

but this comes back with 'error in your SQL syntax'.

 

mySql version 5.0.19

 

Does anyone have any ideas?

 

Thanks

[corbin]

You could just do a subquery, or better yet a join.

 

UPDATE game20sep g1 JOIN game20sep g2 ON g2.Username = g1.Username SET TotalPts = SUM(<all of your columns);

 

 

 

Test that before you run it on live data though, as I could have the logic wrong or a typo.

[nikkicade]

Hi Corbin

 

Tried your code but came back with "Column 'TotalPts' in field list is ambiguous". Not used JOIN before but tried adding g1. before TotalPts and it came back with "Invalid use of Group function". The Username is in the same table as the results and points so do I need Join as I thought this linked or joined tables?

 

All I need to do is write the results of the sum into the TotalPts column but do it by table row as the Username is unique and therefore there is only 1 row per username. If you use:-

 

insert into game20sep (TotalPts) Value (SUM(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts)) on duplicate key update TotalPts=SUM(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts);

 

you get an error "Invalid use of Group function" similar to the update command in first post and if you add 'GROUP BY Username' on the end it says there is an error in the syntax.

 

I am lost with this as I can get the Sum to work but can't seem to write it into the column in the table.

 

Thanks

 

Nikki

[corbin]

Ahh my bad, it should've been

 

 

UPDATE game20sep g1 JOIN game20sep g2 ON g2.Username = g1.Username SET g1.TotalPts = SUM(<all of your columns>);

 

 

Lemme test this my self.  I'll post back in a few minutes.

[nikkicade]

Hi Corbin

 

I tried that but it said Invalid use of group function

 

 

Tried this :-

 

select sum(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) AS TotalPts from game20sep group by Username;

 

and it shows the total In the TotalPts column but it still does not write it to the column.

[corbin]

Oh, that's right.... It would need to be grouped, and grouping can't be done in an update query as far as I know.

 

 

 

I guess a subquery would be one way.

 

 

UPDATE game20sep g1 SET TotalPts = (SELECT SUM(your columns) FROM game20sep g2 WHERE g2.username = g1.username GROUP BY g2.username);

 

 

That might invalid syntax.  Haven't done a subquery in a while.

 

 

Also, you'll probably need to do SUM(col1) + sum(col2) not lump them all in 1 sum.

 

 

 

 

By the way, I think your table structure could be optimized to not run into this problem.  Your table structure sounds kind of bad based on the query you used as an example.

[nikkicade]

Tried that and got an error "You can't specify target table 'g1' for update in FROM clause".

 

As I have a similar problem with the monthly totals and running totals for the year - maybe it would be better to create a totals table and have a TotalPts for each game as well as a monthly total and a running total and then use something like

 

Update totalsable g1 set Game1TotalPts=(SELECT SUM(your columns) FROM game20sep g2 WHERE g2.username = g1.username GROUP BY g3.username);

 

Do you think this would be better?

 

[corbin]

Yeah I think keeping the totals in a different table would make more sense.  Not sure of your table schema though.

[nikkicade]

I will create a new table for totals and see if I can get it to work.

 

Thanks for your help

[nikkicade]

Just tried it with a new table called alltotals. Used this :-

 

update alltotals g1 SET GameTotal= (SELECT SUM(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) FROM game20sep g2 WHERE g2.username = g1.username GROUP BY g2.username);

 

and it worked a treat :-)

 

Thanks again for your help

[corbin]

Didn't help much since you solved your own problem, but uhhhh, no problem ;p.