Menu
Let's say I have a hypothetical table like so that records when some player in some game scores a point:
name points
------------
bob 10
mike 03
mike 04
bob 06
How would I get the sum of each player's scores and display them side by side in one query?
Total Points Table
bob mike
16 07
My (pseudo)-query is:
SELECT sum(points) as "Bob" WHERE name="bob",
sum(points) as "Mike" WHERE name="mike"
FROM score_table
533
To generate and execute this statement dynamically in sql you would need to use the INFORMATION_SCHEMA. COLUMNS table to create a query string then execute it using a prepared statement saved in a variable.
There are two ways to perform the activity: Without replacing the existing column with the CONCAT function. By replacing the existing column using REPLACE() function with CONCAT() function.
When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause.
You can pivot your data 'manually':
SELECT SUM(CASE WHEN name='bob' THEN points END) as bob,
SUM(CASE WHEN name='mike' THEN points END) as mike
FROM score_table
but this will not work if the list of your players is dynamic.
125
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
