CODEWITHSUNDEEP
mysql
Safcblogger
Safcblogger

Reputation: 13

MySQL SELECT * and Percentage

Bit of a newb with MySQL and not proficient enough to understand the logic of the statements I am trying to put together.

I have this query;

SELECT FORMAT(SUM(won/total*100),2) AS PCT FROM table_name WHERE Status='Current'

Which works fine to return the win% for the total sum of all records, but I am looking to return the result by row?

Essentially what I am trying to achieve is the win% for each row using SELECT *, but I can not use SELECT * in combination with working out the percentage.

Or I can and probably have the logic wrong, i'm not sure.

The returned result would be something like;

Manager | Win%

Arsene Wenger | 57.24%

Alex Ferguson | 48.02%

What am I missing?

Upvotes: 1

Views: 249

Answers (3)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Try this Query:

SELECT Manager,FORMAT(SUM(won/total*100),2) AS PCT 
FROM table_name 
WHERE Status='Current'
GROUP BY Manager

If u want for Each Row

SELECT Manager,FORMAT((won/total*100),2) AS PCT 
FROM table_name 
WHERE Status='Current'

Upvotes: 2

MLeblanc
MLeblanc

Reputation: 1884

you have to group rows by the name of the person, something like

SELECT FORMAT(SUM(won/total*100),2) AS PCT FROM table_name WHERE Status='Current' group by manager ;

Upvotes: 1

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

You are missing a GROUP BY statement.
Or, if you wan't to return the %-age for all rows, you must remove SUM since that will aggregate all rows returned into on sum.

Upvotes: 1

Related Questions