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]

Hi Cpreston,

I'm not sure if I understood your request right.

Here is an example:
I have this table

select * from MYTAB1

containing these data

 ....+....1                     
 MC_MAXIMUM                     
     1.0                        
     2.0                        
     3.0                        
     4.0                        
     5.0                        
     6.0                        
     8.0                        
    10.0                        
 ********  End of data  ********

I can use temporary CTE (=Common Table Expression) MYTAB2 with temporary field MY_GROUP and then do GROUP BY on this field:

with MYTAB2 as (
  select 
    MC_MAXIMUM,
    case 
      when MC_MAXIMUM between 1 and  5 then '1-5'
      when MC_MAXIMUM between 6 and 10 then '6-10'
      else 'X'
    end as MY_GROUP
  from MYTAB1
)
select 
  MY_GROUP, 
  count(MC_MAXIMUM) as GROUP_COUNT  
from MYTAB2
group by MY_GROUP
order by MY_GROUP

with this result

 ....+....1....+....2....       
 MY_GROUP    GROUP_COUNT        
   1-5                 5        
   6-10                3        
 ********  End of data  ********

Is this what you needed ?

 

[Cpreston]

Yes something like that

I have entries like below, there is a lot more. I dont quite understand your code but the result looks like what I am after

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

 

[Andrzejek]

Looks like you may want to have another table so you don't hard code values in your SQL:

[pre]
Ranges_Table
ID Low High Range
1 1 5 1-5
2 6 10 6-10
3 11 15 11-15
...[/pre]

---- Andy

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

 

[Cpreston]

Hi mikrom

I have my code as below but ow I am getting an error saying Conversion failed when converting the nvarchar value '16.4 ' to data type int. how do i get round this please

 WITH MyTab AS (
 Select 
 mc_maximum,
    case 
      when MC_MAXIMUM between 1 and  5 then '1-5'
      when MC_MAXIMUM between 6 and 10 then '6-10'
      else 'X'
    end as MY_GROUP
  from MaxFrequency
)
select 
  MY_GROUP,
  count(MC_MAXIMUM) as GROUP_COUNT 
from MYTAB
group by MY_GROUP
order by MY_GROUP

 

[Cpreston]

Hi Andrzejek

Not sure what you mean, for now I am just trying to see if I can group them into a count and then decide what to do with the data as it will become part of a bigger picture.

Thanks for the replies

 

[mikrom]

I used your data

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 )

then modified CASE conditions in the select statement as follows:

with MYTAB2 as (
  select 
    MC_MAXIMUM,
    case 
      when MC_MAXIMUM >= 01 and MC_MAXIMUM <= 05 then '01-05'
      when MC_MAXIMUM >  05 and MC_MAXIMUM <= 10 then '05-10'
      when MC_MAXIMUM >  10 and MC_MAXIMUM <= 15 then '10-15'
      when MC_MAXIMUM >  15 and MC_MAXIMUM <= 20 then '15-20'
      when MC_MAXIMUM >  20 and MC_MAXIMUM <= 25 then '20-25'
      when MC_MAXIMUM >  25 and MC_MAXIMUM <= 30 then '25-30'
      else 'OUT_OF_RANGE'
    end as MY_GROUP
  from MYTAB1
)
select
  MY_GROUP, 
  count(MC_MAXIMUM) as GROUP_COUNT  
from MYTAB2
group by MY_GROUP
order by MY_GROUP

and got this:

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

IMO this should be what you need.

 

[Cpreston]

Hi mikrom

Brilliant thanks but I am still getting this in the result Conversion failed when converting the nvarchar value '16.4 ' to data type int.

I have tried all sorts of convert or cast commands but cant seem to get the syntax right, any ideas how to put it into the code to stop it happneing

Many Thanks

 

[mikrom]

I have my code as below but ow I am getting an error saying Conversion failed when converting the nvarchar value '16.4 ' to data type int.

Don't understand what conversion you do ...
I created the exercise table MYTAB1 as

create table MYTAB1 (     
  mc_maximum decimal(3,1)
)

then inserted into it the data you suggested and evrything works without an conversion error

 

[Cpreston]

I am adapting your code to use on my table as below, when I run it I get the Conversion failed when converting the nvarchar value '16.4 ' to data type int
The field mc_maximum is nvarchar(10)

with MYTAB2 as (
  select 
    MC_MAXIMUM,
    case 
      when MC_MAXIMUM >= 01 and MC_MAXIMUM <= 05 then '01-05'
      when MC_MAXIMUM >  05 and MC_MAXIMUM <= 10 then '05-10'
      when MC_MAXIMUM >  10 and MC_MAXIMUM <= 15 then '10-15'
      when MC_MAXIMUM >  15 and MC_MAXIMUM <= 20 then '15-20'
      when MC_MAXIMUM >  20 and MC_MAXIMUM <= 25 then '20-25'
      when MC_MAXIMUM >  25 and MC_MAXIMUM <= 30 then '25-30'
      else 'OUT_OF_RANGE'
    end as MY_GROUP
  from MaxFrequency
)
select
  MY_GROUP, 
  count(MC_MAXIMUM) as GROUP_COUNT  
from MYTAB2
group by MY_GROUP
order by MY_GROUP

 

[mikrom]

Now I see: your data type of MC_MAXIMUM is not numeric but NVARCHAR and in my example im using numeric data type DECIMAL

 

[Cpreston]

So is there a way to convert from nvarchar to decimal, the table I am using is a test one so I cannot harm anything or could it convert within the command?

 

[mikrom]

I maked this modification:
created the table with NVARCHAR, inserted the decimal values in it and then in the CASE conditions converted the NVARCHAR fields into DECIMAL(3, 1) and it seems to work for me.
Try this:

create or replace table MYTAB1 (
  MC_MAXIMUM nvarchar(10) not null
)
;

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 )
;

with MYTAB2 as (
  select 
    MC_MAXIMUM,
    case 
      when DECIMAL(MC_MAXIMUM, 3, 1) >= 01 and DECIMAL(MC_MAXIMUM, 3, 1) <= 05 then '01-05'
      when DECIMAL(MC_MAXIMUM, 3, 1) >  05 and DECIMAL(MC_MAXIMUM, 3, 1) <= 10 then '05-10'
      when DECIMAL(MC_MAXIMUM, 3, 1) >  10 and DECIMAL(MC_MAXIMUM, 3, 1) <= 15 then '10-15'
      when DECIMAL(MC_MAXIMUM, 3, 1) >  15 and DECIMAL(MC_MAXIMUM, 3, 1) <= 20 then '15-20'
      when DECIMAL(MC_MAXIMUM, 3, 1) >  20 and DECIMAL(MC_MAXIMUM, 3, 1) <= 25 then '20-25'
      when DECIMAL(MC_MAXIMUM, 3, 1) >  25 and DECIMAL(MC_MAXIMUM, 3, 1) <= 30 then '25-30'
      else 'OUT_OF_RANGE'
    end as MY_GROUP
  from MYTAB1
)
select
  MY_GROUP, 
  count(MC_MAXIMUM) as GROUP_COUNT  
from MYTAB2
group by MY_GROUP
order by MY_GROUP
;

I got the result

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

 

[Cpreston]

Ok I now have a table called MYTAB1 with the entries you gave in the Insert command. I then run the main script but I now get 'DECIMAL' is not a recognized built-in function name. Sorry about this


with MYTAB2 as (
select
MC_MAXIMUM,
case
when DECIMAL(MC_MAXIMUM, 3, 1) >= 01 and DECIMAL(MC_MAXIMUM, 3, 1) <= 05 then '01-05'
when DECIMAL(MC_MAXIMUM, 3, 1) > 05 and DECIMAL(MC_MAXIMUM, 3, 1) <= 10 then '05-10'
when DECIMAL(MC_MAXIMUM, 3, 1) > 10 and DECIMAL(MC_MAXIMUM, 3, 1) <= 15 then '10-15'
when DECIMAL(MC_MAXIMUM, 3, 1) > 15 and DECIMAL(MC_MAXIMUM, 3, 1) <= 20 then '15-20'
when DECIMAL(MC_MAXIMUM, 3, 1) > 20 and DECIMAL(MC_MAXIMUM, 3, 1) <= 25 then '20-25'
when DECIMAL(MC_MAXIMUM, 3, 1) > 25 and DECIMAL(MC_MAXIMUM, 3, 1) <= 30 then '25-30'
else 'OUT_OF_RANGE'
end as MY_GROUP
from MYTAB1
)
select
MY_GROUP,
count(MC_MAXIMUM) as GROUP_COUNT
from MYTAB2
group by MY_GROUP
order by MY_GROUP

 

[mikrom]

Then forget the DECIMAL() function, probably MS SQL server does not have it.
But why you got the error "Conversion failed when converting the nvarchar value '16.4 ' to data type int.", why it tries to convert it to integer and not float or double ? ... maybe if you add in the CASE-conditions the decimal points in the number values, then it tries to convert it to float.

Please try this what it says:

with MYTAB2 as (
  select 
    MC_MAXIMUM,
    case 
      when MC_MAXIMUM >= 01.0 and MC_MAXIMUM <= 05.0 then '01-05'
      when MC_MAXIMUM >  05.0 and MC_MAXIMUM <= 10.0 then '05-10'
      when MC_MAXIMUM >  10.0 and MC_MAXIMUM <= 15.0 then '10-15'
      when MC_MAXIMUM >  15.0 and MC_MAXIMUM <= 20.0 then '15-20'
      when MC_MAXIMUM >  20.0 and MC_MAXIMUM <= 25.0 then '20-25'
      when MC_MAXIMUM >  25.0 and MC_MAXIMUM <= 30.0 then '25-30'
      else 'OUT_OF_RANGE'
    end as MY_GROUP
  from MYTAB1
)
select
  MY_GROUP, 
  count(MC_MAXIMUM) as GROUP_COUNT  
from MYTAB2
group by MY_GROUP
order by MY_GROUP

 

[Andrzejek]

What I mean is - right now you have [red]hard-coded values in your SQL[/red], which is fine for testing and trying to make it work. But for production code you do not want the hard-code values, especially if/when they change (and they will change no matter what your boss says )

So if now you have this:[pre]
case
when MC_MAXIMUM >= [red]01[/red] and MC_MAXIMUM <= [red]05[/red] then [red]'01-05'[/red]
when MC_MAXIMUM > [red]05[/red] and MC_MAXIMUM <= [red]10[/red] then [red]'05-10'[/red]
when MC_MAXIMUM > [red]10[/red] and MC_MAXIMUM <= [red]15[/red] then [red]'10-15'[/red]
when MC_MAXIMUM > [red]15[/red] and MC_MAXIMUM <= [red]20[/red] then [red]'15-20'[/red]
when MC_MAXIMUM > [red]20[/red] and MC_MAXIMUM <= [red]25[/red] then [red]'20-25'[/red]
when MC_MAXIMUM > [red]25[/red] and MC_MAXIMUM <= [red]30[/red] then [red]'25-30'[/red]
else 'OUT_OF_RANGE'[/pre]

That's why I suggest to have a table with those values, eventually...

---- Andy

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

 

[Cpreston]

Thanks again for the replies

Currently if I use the table MaxFrequency I am getting the "Conversion failed when converting the nvarchar value '16.4 ' to data type int problem, so struggling to make it work.

I have created a table called MYTAB1 and made the field MC_MAXIMUM a decimal. Then the code works.

So I then re-did everything and created it as nchar(10) and tried to run the code below from mikrom last post and I now I am getting Arithmetic overflow error converting nvarchar to data type numeric. which is a new one

with MYTAB2 as (
select
MC_MAXIMUM,
case
when MC_MAXIMUM >= 01.0 and MC_MAXIMUM <= 05.0 then '01-05'
when MC_MAXIMUM > 05.0 and MC_MAXIMUM <= 10.0 then '05-10'
when MC_MAXIMUM > 10.0 and MC_MAXIMUM <= 15.0 then '10-15'
when MC_MAXIMUM > 15.0 and MC_MAXIMUM <= 20.0 then '15-20'
when MC_MAXIMUM > 20.0 and MC_MAXIMUM <= 25.0 then '20-25'
when MC_MAXIMUM > 25.0 and MC_MAXIMUM <= 30.0 then '25-30'
else 'OUT_OF_RANGE'
end as MY_GROUP
from MYTAB1
)
select
MY_GROUP,
count(MC_MAXIMUM) as GROUP_COUNT
from MYTAB2
group by MY_GROUP
order by MY_GROUP

 

[Andrzejek]

Please, have MC_MAXIMUM as decimal, not as nchar(10) or any other 'characters'/text type.
Why? You are asking for trouble. You do not want data like:

[pre]
MC_MAXIMUM
7.2
10.3[red]
ABC[/red]
21.2
21.5[red]
XYZ[/red]
21.6
19.9
19.7
[/pre]
... Do you

---- Andy

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

 

[Cpreston]

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

 

[mikrom]

It's clear that storing numbers in strings is not good and cause troubles, but it can happen.
Then the another question has come up:
Given a numeric value from the NVARCHAR data type, how do you convert it into a number in MS SQL?
What I posted worked for me, but I don't have MS SQL, I'm on IBM DB2. So I cannot help you with this, I'm sorry.