Click here to Skip to main content
15,562,222 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Friends,

i have the following list in mySql Database:

name	word		 	hit
Bill	performance		2
Anna	performance		5
John	performance		3
Bill	java			0
Anna	java			1
John	java			3
Bill	test			4
Anna	test			5
John	test			1

but i need it to be like that:

name	sum(hit)		words_count
Bill	6		performance = 2  java = 0, test = 4
Anna	11		performance = 5  java = 1, test = 5
John	7		performance = 3  java = 3, test = 1

I cant manage to write a sql statement to get the results like that.
Could you guys please give me a hint. i've been trying it all day.

Thanks a lot in advance

What I have tried:

sum(), groupby, subquerys, google
Updated 17-Jun-21 3:45am

Start here: SQL GROUP BY Statement[^]
You'll probably need to use a couple of those, combined with SQL Joins[^]
Share this answer
I found it out. Thanks anyway.

 SELECT name, sum(hit) as points,
   GROUP_CONCAT(concat(word, '->'), concat(hit, ' ') separator ' ' ) as words
FROM results
GROUP BY name;
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