skiracer37 skiracer37 - 1 year ago 63
MySQL Question

Need help trying to rank grouped values by month

I have a dataset that looks like this (there are other columns, but these are the relevant ones):

**Month | Revenue | Segment**
01/01/2016 | $40,000 | Seg XYZ

I'm trying to assign a rank to each segment based on revenue by month. So if there is a second row for January of $30,000 for a different segment, I would have a rank of 1 for the first row, and a 2 for the second row. This will repeat across segments and months.

This is my query:


,case when @d=month then @r:=@r+1

else @r:=1 end as rank

,@d:=month as stuff


(select month

,sum(Revenue) as Revenue

,segment from final_audience_usage_file

group by month, segment) a


(select @r:=0,@d:=0)d

order by month, revenue desc

I keep getting this error: Data truncated: Incorrect date value: '0' for column 'month' at row 1

I checked the data, and there are no zero's, but the fact that it's in the first row leads me to believe that something that I'm doing is changing that to zero. Any thoughts?

Answer Source

You have to change the value that variable @d is initialized to. So instead of:

(select @r:=0,@d:=0)


(select @r:=0,@d:='1900-01-01')

My guess is that date '1900-01-01' is not used in your table. This way the comparison of the CASE expression can be executed without any problems.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download