Getting Tier display - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting Tier display

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-12-09 : 00:01:32

I am able to do this using UNION clauses, but is there a better way

Create Table Test(amount)

Insert into test(Amount)
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union

What I want to output is this
Desc|Count|Sum
'less than or equal to 30', 3 , 60
'31 to 50', 2, 90
'51 to 70', 2, 130
'over 70',1,80

I can currently achieve this by a union query like below

Select 'less than or equal to 30',count(*),sum(amount)
from test
where amount <= 30
Union all
Select '31 to 50',count(*),sum(amount)
from test
where amount between 30 and 50
Union all
etc.

Is there a better way to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 02:37:55

Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-10 : 17:31:12

Uh huh... except this isn't a cross-tab problem...

Vinnie,

This should do the trick... notice the extra "<" in the <= 30 description to take advantage of the natural sort order...

Create Table #Test(amount INT)

Insert into #Test(Amount)
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 

 SELECT [Desc] = 
            CASE
            WHEN Amount <= 30 THEN '<Less than or equal to 30'
            WHEN Amount BETWEEN 31 AND 50 THEN '31 to 50'
            WHEN Amount BETWEEN 51 AND 70 THEN '51 to 70'
            WHEN Amount  > 70 THEN 'Over 70'
            END,
        [Count] = COUNT(Amount),
        [Sum]   = SUM(Amount)
   FROM #Test
  GROUP BY  CASE
            WHEN Amount <= 30 THEN '<Less than or equal to 30'
            WHEN Amount BETWEEN 31 AND 50 THEN '31 to 50'
            WHEN Amount BETWEEN 51 AND 70 THEN '51 to 70'
            WHEN Amount  > 70 THEN 'Over 70'
            END
  ORDER BY [Desc]

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 17:40:10

If you somehome, in the future, can't rely on the natural order, this can be one approach.

-- prepare test data
declare	@test table (amount int)

Insert	@test
select	10 union all
select	20 union all
select	30 union all
select	40 union all
select	50 union all
select	60 union all
select	70 union all
select	80

-- do the work
SELECT		SUBSTRING(q.[Desc], 3, 8000) AS [Desc],
		COUNT(*) [Count of Amount],
		SUM(Amount) [Sum of Amount]
FROM		(
			SELECT	CASE
					WHEN Amount <= 30 THEN '1#Less than or equal to 30'
					WHEN Amount BETWEEN 31 AND 50 THEN '2#31 to 50'
					WHEN Amount BETWEEN 51 AND 70 THEN '3#51 to 70'
					WHEN Amount  > 70 THEN '4#Over 70'
				END AS [Desc],
				Amount
			FROM	@Test
		) q
GROUP BY	SUBSTRING(q.[Desc], 3, 8000)
ORDER BY	MIN(LEFT(q.[Desc], 1))

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources