1

Let's say I have a table like this

enter image description here

I want to calculate the frequency ( How many times that product exists in that price range ), in intervals of "50"

So eventually it will give me a table like

enter image description here

Interval for range will be lets pretend a fixed 50 We don't know highest and lowest price of these each products.

So I will run the query and it will give a table as shown above.

I got a query like this but it didn't work. It gives this error ( I cannot edit server config )

This is the query

select product, count(*) as frequency,
       floor(price / 50) as range_start, floor(price / 50) + 50 as range_end
from t 
group by product, floor(price / 50)
order by product, min(price)

This is the error

 [42000][1055] Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

After that I tried this ( changed floor(price / 50) to floor(min(price) / 50)

select product, count(*) as frequency,
       floor(min(price) / 50) as range_start, floor(min(price) / 50) + 50 as range_end
from t 
group by product, floor(price / 50)
order by product, min(price)

This query worked but it gives ranges like

enter image description here

0

1 Answer 1

0
select product, count(*) as frequency,
       50 * floor(price / 50) as range_start,
       50 * (floor(price / 50) + 1) as range_end
group by product, 50 * floor(price / 50), 50 * (floor(price / 50) + 1)
order by product, min(price)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.