CODEWITHSUNDEEP
phpmysqlsqlgroup-by
Ruben
Ruben

Reputation: 192

SQL totals by percentages calculation

I want a SQL query to go from 

Shop    Regio    target1    sold1    target2    sold2 
shop1   regioA   6          3        10         5
shop2   regioA   4          2        4          2
shop3   regioB   6          0        3          0
shop4   regioC   9          9        8          8
shop5   regioB   8          4        2          1

to 

regioC      100%
regioA       50%
regioB       25%

(nevermind the numbers, I just made these up)

I tried using this but it didn't work:

SELECT regio, SUM((sold2/target1)+(sold2/target2)) AS total 
FROM  `winkels` GROUP BY `regio` ORDER BY total DESC

Any ideas how to make it right?

Upvotes: 0

Views: 99

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

This should work. You were trying to add percentages together, instead of dividing by the totals. 

SELECT regio, 100*SUM(sold1+sold2)/sum(target1 +target2) AS total 
FROM  `winkels` GROUP BY `regio` ORDER BY total DESC

Upvotes: 3

Related Questions