CODEWITHSUNDEEP
mysql
NekoLopez
NekoLopez

Reputation: 609

Calculating percent of each row value in MySQL in a certain date range

I want to get the first 4 rows with the highest value in a certain date range of a table in a mysql database. So I can do it with this code line, for example:

SELECT MONTH(date) as month, amount from sales where date >='2014-01-01' AND date <='2014-12-31' ORDER BY amount DESC LIMIT 4

But I want to add a new column with the percentage of each value, I tried with this line:

SELECT MONTH(date) as month, amount, round(amount*100/sum(amount) ,1) as 'percent' from sales where date >='2014-01-01' AND date <='2014-12-31' ORDER BY `amount` DESC LIMIT 4

But it doesn't work. How can I get it? I'd like some help.

Upvotes: 1

Views: 790

Answers (1)

mcv
mcv

Reputation: 1460

Here's one way of doing it. Providing snippets of raw data also helps. Creating an sql fiddle helps even more!!

select month, amount, round(amount*100/total,1) as percent
  from 
  (
    select MONTH(date) as month, amount 
      from sales 
      where date >='2014-01-01' AND date <='2014-12-31'
     ORDER BY amount DESC LIMIT 4
  ) c JOIN (
    select sum(amount) as total
      from sales 
      where date >='2014-01-01' AND date <='2014-12-31'
  ) t

Upvotes: 1

Related Questions