simple to some, kinda hard for me.. sql counting

[0o0o0]

I have a table.. explain this as quick as I can..

 

 

ok ..  you all know what this does correct?

 

select playerref, count(playerref) as gamesplayed FROM contest group by playerref 

say I have 51 rows.. Joe is 50 of them.

and 1 row is  pete..

 

the above will return two rows as the result..
one  joe 50

one  pete 1

 

what im trying to do is keep all 51 rows..

but at the ends is a new column in all joes 50 rows, in this new column it shows 50

and the last row  it shows one row pete 1

Basically I want the count in a new column at the end of every row.

 

possible? thanks anyone who can help.

 

 

[benanamen]

 I understand what your asking. My question is, why do you want to do this? What is the overall task you are trying to accomplish? And I don't mean how you are trying to accomplish it. I sense an XY Problem. See my signature for explanation.

[0o0o0]

honestly... Im using excel and i5 laptop, and its all a P.O.S.  I have 400,000 rows and i simply want all the names to have the number of games played at the end of every row... I started it at 8:30am.. its now 3:06pm...  so I was messing around and threw the list into a database and its a hell of alot faster..

So my plan let sql do the calcs quicker.. export back to excel.. voilia. sorting simpler.

 

but ah! since you asked me this.. maybe sorting the excel sheet by names then doing the calcs is faster.. thx.

as for the question above im still looking for an answer.

Edited November 11, 2016 by 0o0o0

[Jacques1]

Join the original table with the result set of your current query.

SELECT contest.playerref, contest_stats.games_played
FROM contest
JOIN (
  SELECT playerref, COUNT(*) AS games_played
  FROM contest
  GROUP BY playerref
) AS contest_stats USING (playerref)
;

Proper database systems like PostgreSQL have window functions for that purpose, but MySQL doesn't and probably never will.

[0o0o0]

your xy is kinda freaky.. 9 hours.. waiting otday.. and well. ya its half done once I sorted by name 5 mins lol..

 

 

but I still want the sql answer.

-----

 

 

Hey thanks

Jacques1! Edited November 11, 2016 by 0o0o0

[0o0o0]

yep sql beats it hands down ..

 

thanks all!