How to group results and count items it each range group

[erezvol]

I have a column of profit. I need to count how hany results are in ranges of 5

-5<=Profit<0, 0<=profit<5, 5<= profit<10

In other words group profit from 0 up and down in gaps of 5 and count how many results in each group. Value of profit is not limited I can’t tell in advance the min and max values. How do I give each range an alias

[r937]

please test this and let us know how it goes…

SELECT Profit/5 AS profit_group
     , COUNT(*) AS how_many
  FROM ...
GROUP
    BY Profit/5  

[erezvol]

First of all thanks for your quick response. This is not exactly what I ment.
Profit/5 gives the value of Profit divided by 5 e.g. if Profit=10 then profit=10/5=2.

I want to count how many time I have a profit value ranging fron 0 to 5, 5 to 10 and so on
I also want the same from0 to -5, -5 to -10.

It is important to me not to have a range like -3 to 2 .
I want 0>=profit<5 and up (count how many time I get profit value at this range
ANd I want -5>=profit<0
So i want 0 included and up. And less than 0 down.

I don’t know in advance the min / max values.

Can it be done in SQL ?

[m_hutley]

@erezvol

Profit=10 then profit=10/5=2.

And when you get your grouped results back, the profit GROUP is “like -3 to 2”… and if you multiply those group values by 5 again… you’ll get the lower bound of the group. (Note: We are making an assumption you’re using integers rather than floats. If decimal points are involved, the code gets a little more complex.)

You could do it with a CASE statement, if you really need to only see 3 results.

[DaveMaxwell]

@m_hutley, how would you use a CASE statement in this case? I’m just curious how you’d approach it without it being gangly. Perhaps my brain is just fried from work at the moment.

Personally, I’d make a table of the profit ranges (you could use a temp table if you’re not going to do this often) and join, using Rudy’s original query as a sub-query…

CREATE TABLE ProfitRange(low integer, high integer);
INSERT INTO ProfitRange (low, high) VALUES(-5, 0);
INSERT INTO ProfitRange (low, high) VALUES(0, 5);
INSERT INTO ProfitRange (low, high) VALUES(5, 10);
INSERT INTO ProfitRange (low, high) VALUES(10, 15);

Note the JOIN condition for the sub-query. That’s using that instead of BETWEEN because between is inclusive, not exclusive and since you don’t want to include the high in the grouping…

SELECT PR.Low
     , PR.High
     , SUM(PM.how_many) AS RangeCount
  FROM ProfitRange AS PR
  JOIN (SELECT Profit/5 AS profit_group
             , COUNT(*) AS how_many
          FROM ....
         GROUP BY Profit/5) AS PM ON PM.profit_group >= PR.Low AND PM.profit_group < PR.High
  GROUP BY PR.Low
      , PR.High

[m_hutley]

Sorry, i was misreading his response ( I want 0>=profit<5 and up) as he only wanted 3 groups. More coffee required.

I’d stick with the grouping, but handle the ‘and up’ bit at the server scripting end.

Order your results by group number, select all results into an array, and then do a sum of the array slice from X to the end of the array.