Help with grouping and sorting data in SQL 2000

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

General SQL Server Forums

New to SQL Server Programming

Help with grouping and sorting data in SQL 2000

Author

Topic

brownjamesa
Starting Member

13 Posts

Posted - 2010-06-10 : 11:33:45

Hi,

I am trying to re-create a report that was extracting data from an access database to extracting the same data from a sql database.

The current Access report, uses the 'Group Interval' setting within the Sorting & Grouping options.

What I am trying to achive is following report from the below data:

Data:
------

Name,Count
User01,101
User02,2
User03,4
User04,77
User05,51
User06,33
User07,120
User08,23
User09,33
User10,222

I would like to create a report like below:

Count, Num
----- ----
0-50 5
50-100 2
100-150 2
150-200 0
200-250 1

Any help would be really helpful.

Cheers
James

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-10 : 13:59:15

[code]SELECT [Group], sum(case when [count] between start and stop then 1 else 0 end)
FROM @table
CROSS JOIN

( select '0 - 50' as [Group] ,0 as Start,49 as stop union
select '50 - 99' ,50,99 union
select '100 - 149', 100,149union
select '150 - 199',150,199union
select '200 - 249',200,249
) a

GROUP BY [Group][/code]

Jim

Everyday I learn something that somebody else already knew

brownjamesa
Starting Member

13 Posts

Posted - 2010-06-11 : 10:06:34

Hi Jim,

Thanks fot taking the time to reply to my post.

I have had a look at what you have recommened and could not get that to work, but looking at your query I manage to get the following two working:

#1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT DataExtractedDate,
Count(User) as TotalMailboxes,
Count(CASE WHEN Size Between 0 AND 50 THEN Size ELSE NULL END) as '0-50_Tot',
Sum(CASE WHEN Size Between 0 AND 50 THEN Size ELSE NULL END) as '0-50_Sum',
AVG(CASE WHEN Size Between 0 AND 50 THEN Size ELSE NULL END) as '0-50_Avg',
Count(CASE WHEN Size Between 51 AND 100 THEN Size ELSE NULL END) as '50-100_Tot',
Sum(CASE WHEN Size Between 51 AND 100 THEN Size ELSE NULL END) as '50-100_Sum',
AVG(CASE WHEN Size Between 51 AND 100 THEN Size ELSE NULL END) as '50-100_Avg'
FROM DBTable
Group By DataExtractedDate
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT '0-50' as Sizeby50MB,
Count(Size) as Total,
Sum(Size) as TotalSize,
AVG(Size) as AVGSize
FROM DBTable
Where (Size Between 0 AND 50)
UNION ALL

SELECT '50-100' as Sizeby50MB ,
Count(Size) as Total,
Sum(Size) as TotalSize,
AVG(Size) as AVGSize
FROM DBTable
Where (Size Between 51 AND 100)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#1 runs faster as it only passes the data onece, while the second one (#2) runs slower due to passes tht data twice.

I perfer the way that data is display in option #2.

When I tried your query I think I got the syntax wrong, could you please type it again but in releation to my table name and fields.

Cheers
James

brownjamesa
Starting Member

13 Posts

Posted - 2010-06-11 : 11:53:43

Hi Jim again...

I think I have sorted it now, I am using the below query:

SELECT CASE
WHEN Size IS NULL THEN '-'
WHEN Size <= 50 THEN '0-50'
WHEN Size <= 100 THEN '51-100'
WHEN Size <= 150 THEN '101-150'
ELSE 'Over 150+'
END 'Split',
COUNT(Size) 'iTot',
SUM(Size) 'iSum',
AVG(Size) 'iAvg'

FROM DBTable

GROUP BY CASE
WHEN Size IS NULL THEN '-'
WHEN Size <= 50 THEN '0-50'
WHEN Size <= 100 THEN '51-100'
WHEN Size <= 150 THEN '101-150'
ELSE 'Over 150+'
END

Results:
---------
Split iTot iSum iAvg
0-50 23433 234234 28
50-100 3444 34334 18
...
...
..

Cheeers
James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources