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

3
  • did you try to add "floor(price / 50) + 50" to the GROUP BY part of the first query? Jun 13, 2020 at 8:58
  • @Nikita it worked. But range is 2,52 - 3,53 rather than 0,50 -50,100
    – SNaRe
    Jun 13, 2020 at 9:08
  • 1
    Please note that images are frowned upon here for the reasons outlined in this link. In future, could you please post your table structures as DDL and your data as DML - perhaps as a fiddle (but be sure to post that information here also). Help us to help you!
    – Vérace
    Jun 13, 2020 at 9:21

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)
2
  • [HY000][1111] Invalid use of group function The problem is with "min" in group by I think since I got this error in the IDE "Aggregate calls are not allowed here "
    – SNaRe
    Jun 13, 2020 at 9:36
  • @SNaRe, sorry, I copy+pasted the wrong part :) Fixed. Jun 13, 2020 at 9:37

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.