Dynamic Grouping and Case Statements 2

[Guru7777]

Hello all,

I have an issue where I have data that I need to group out. It is in a table like the following

Name     Income
----     ------
Phil     100
Mary     500
Bill     450
Will     45

I need to produce a count of how many people fall in each range, with that range as my left side. I can produce this

0-49        50-99       100-499     500-1000    Other       
----------- ----------- ----------- ----------- ----------- 
1           0           2           1           0

But I need it to look like this:

bracket  number      
-------- ----------- 
0-49     1
50-99    0
100-499  2
500-1000 1
Other    0

Can anyone help?

Thanks in advance.

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[gmmastros]

Select '0-49' As Bracket,
       Count(1) As Number
From   [!]TableName[/!]
Where  Income Between 0 and 49

Union All

Select '50-99',	
        Count(1)
From    [!]TableName[/!]
Where   Income Between 50 and 99

Union All

Select '100-499',	
       Count(1)
From   [!]TableName[/!]
Where  Income Between 100 and 499

Union All

Select '500-1000',	
       Count(1)
From   [!]TableName[/!]
Where  Income Between 500 and 1000

Union All

Select  'Other',	
        Count(1)
From    [!]TableName[/!]
Where   Income > 1000

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Nah, you can do it better

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Guru7777]

How so?

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[gmmastros]

OK, give me a minute.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

here is a kludge

create table #test (name varchar(50),income int)

insert into #test values('Phil',100)
insert into #test values('Mary',500)
insert into #test values('Bill',450)
insert into #test values('Will',45)
insert into #test values('Bill',45)


select sum(case when income  is null then 0 else 1 end),beginVal,EndVal 
from #test t 
right join(select 1 as beginVal,49 as EndVal
union all
select 50,99
union all
select 100,499
union all
select 500,1000
union all
select 1001,1000000) x on t.income between x.beginVal and x.EndVal
group by beginVal,EndVal

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

and of course if you had the brackets in a seperate table you could do this

create table #test (name varchar(50),income int)

insert into #test values('Phil',100)
insert into #test values('Mary',500)
insert into #test values('Bill',450)
insert into #test values('Will',45)
insert into #test values('Bill',45)


select * into #testCategories from (select 1 as beginVal,49 as EndVal
union all
select 50,99
union all
select 100,499
union all
select 500,1000
union all
select 1001,1000000) zz

now all you need is

select sum(case when income  is null then 0 else 1 end),beginVal,EndVal 
from #test t right join #testCategories x on t.income between x.beginVal and x.EndVal
group by beginVal,EndVal

Now it's waiting for vongrunt's SIGN magic

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Something like this...

Declare @T Table(Name VarChar(10), Income Integer)

Insert Into @T Values('Phil',     100)
Insert Into @T Values('Mary',     500)
Insert Into @T Values('Bill',     450)
Insert Into @T Values('Will',     45)
Insert Into @T Values('John',     2000)

Select	A.Min,
		A.Max,
		Count(1)
From	@T As T
		Left Join (
			Select 0 As Min, 49 As Max
			Union All
			Select 50,99
			Union All
			Select 100,499
			Union All 
			Select 500,1000
			) As A
			On T.Income Between A.Min And A.Max
Group By A.Min, A.Max

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Yup, I thought exactly about that - join on table holding bracket ranges. The rest is face-lifting (displaying "min-max" or "Other" for NULLs)

Now... we did real-life thing. How about some brain exercise? Doing the same without bracket table or UNIONs? .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

Or this... I think this one is a little better.

Declare @T Table(Name VarChar(10), Income Integer)

Insert Into @T Values('Phil',     100)
Insert Into @T Values('Mary',     500)
Insert Into @T Values('Bill',     450)
Insert Into @T Values('Will',     45)
Insert Into @T Values('John',     2000)

Select	Bracket,
		Count(1)
From	(
		Select	Case When Income Between 0 And 49 Then '0-49'
					 When Income Between 1 And 99 Then '49-50'
					 When Income Between 100 And 499 Then '100-499'
					 When Income Between 500 And 1000 Then '500-1000'
					 Else 'Other'
				End As Bracket
		From	@T
		) A
Group By Bracket

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

It still does not display empty brackets (50-99, the one with typos).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

OK, I restrained myself from doing SIGN magic :X

-- set ansi_warnings off
select	convert(varchar, lowlimit) + coalesce('-' + convert(varchar, hilimit-1), '+') as bracket, number
from
(	select R.lowlimit, R.hilimit, count(T.income) as number
	from
	(	select 0 as lowlimit, 50 as hilimit union all
		select 50, 100 union all
		select 100, 500 union all
		select 500, 1000 union all
		select 1000, null
	) R
	left outer join myTable T on T.income >= R.lowlimit and T.income < R.hilimit
	group by R.lowlimit, R.hilimit
) blah
order by lowlimit

Challenge for doing it without range table is still opened though.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]