Frequency Grouping 2

[Cpreston]

Hi

I have a small query I am testing with to get the count of the frequency of a column called mc_maximum. It works fine however I have realised there are many reesults.

They range from 1.1 to 30 with count next to them being correct. IS there away I can group them somehow so for example anything between 1 to 5 in the mc_maximum column is counted, then 6 to 10 counted is counted etc.... hope that makes sense. I have added my small query that gets the current results. Any ideas please, thanks in advance

 Select mc_maximum, count(mc_maximum) AS Frequency
 From   MaxFrequency
 Group By mc_maximum
 Order BY count(mc_maximum) ASC

 

[mikrom]

Quite right I do not, luckily we are in the testing stage so I can ask for any data to be made numeric or decimal at this stage.
It works ok with decimal so thank you for your replies

When you can solve it at this way, then it's best, you don't need to bother with conversion issues.

 

[Cpreston]

Thanks mikrom you have been a big help and certainly pointed me in the right direction.

 

[mikrom]

But then we have the more elegant solution proposed by Andy - the solution with additional table with defined ranges

 

[Cpreston]

Could be above my SQL skills but I see the making a value table would be useful but not sure how it would fit together to do a count on the results from the MaxFrequency.mc_maximum results.

Ranges_Table
ID Low High Range
1 1 5 1-5
2 6 10 6-10
3 11 15 11-15
...

 

[mikrom]

I tried it with the range table as suggested by Andy, but named it MYGROUPS to be compatible with the naming in previous select

create or replace table MYTAB1 (
  MC_MAXIMUM decimal(3, 1)
)
;

insert into MYTAB1 values (7.2), (10.3), (21.2), (21.5),
(21.6), (19.9), (19.7), (7.7), (20.3), (6.5), (20.9), (22.2),
(21.9), (22), (10.5), (22.4), (10.1), (20.6), (2.7 )
;

select * from MYTAB1
;

create or replace table MYGROUPS (
  GROUP_ID   decimal (2, 0),
  VAL_FROM   decimal (2, 0),
  VAL_TO     decimal (2, 0),
  MY_GROUP   character(5)
)
;

insert into MYGROUPS values 
  (1, 01, 05, '01-05'),
  (2, 05, 10, '05-10'),
  (3, 10, 15, '10-15'),
  (4, 15, 20, '15-20'),
  (5, 20, 25, '20-25'),
  (6, 25, 30, '25-30')
;

select * from  MYGROUPS
;

select 
  t2.MY_GROUP,
  count(t1.MC_MAXIMUM) as GROUP_COUNT   
from 
  MYTAB1 t1 
  join
  MYGROUPS t2 on 
    t1.MC_MAXIMUM > t2.VAL_FROM and t1.MC_MAXIMUM <= t2.VAL_TO 
group by t2.MY_GROUP
order by t2.MY_GROUP
;

the result is the same as before

MY_GROUP GROUP_COUNT
01-05	          1
05-10	          3
10-15	          3
15-20	          2
20-25	         10

 

[Andrzejek]

First record in MYGROUPS may need to be changed to[tt]
(1, [red]00[/red], 05, '0[red]0[/red]-05'),[/tt]
to 'catch' values >0 but <=1, but that's up to Cpreston to decide

But the code looks a LOT 'cleaner'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Sorry for the late reply. Just tried all the code from mikrom last post and all works fine. I change the code as suggested by Andrzejek to 00 also. This as given me a great udnertsanding of creating a table to hold these figures. Thanks for all the help.