Grouping chart columns in the same value range

Question

4.00/5 (1 vote)

See more:

Hello !
Here is my problem. I'm working on SQL Server 2008R2 with SSRS.
I got a simple dataset with two fields as for exemple :

Weight | Grade
---------------
50     | 1.20
50     | 1.00
50     | 1.12
50     | 2.34
50     | 1.75
50     | 1.73

And I would like to draw a chart with Grade in X axis and Sum(Weight) as Y axis.
The problem is, I've got a lot of different values for grades and I have to group the values in order to get one column for each range. If i haven't made myself clear yet, here is what I want :

Weight
200|
   |
150|
   |
100|
   |             |
50 |             |                 |
   |_____________|_________________|___|________
   0   0.5    1    1.3   1.5   1.7   2   2.5
                     Grade

That is to say we have 150 between 1 and 1.3, 100 between 1.7 and 2... etc.
I tried to do this with the intervals but it seems to be only for labels... I tried and googled without any explanation on how to do that. I can only manage to do a "round" on the "Grade" value, but that's not helpful as I want to be able to choose the values delimiting a specific range.

Thanks in advance for your help !

Posted 16-Dec-11 4:48am

thilitium

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2011-12-30T10:37:00

Try to use ROUND()[^] function.

Example:

SQL

SELECT 1.23 [Value], ROUND(1.23,1) AS [RndValue]
UNION ALL
SELECT 1.24 [Value], ROUND(1.24,1) AS [RndValue]
UNION ALL
SELECT 1.25 [Value], ROUND(1.25,1) AS [RndValue]
UNION ALL
SELECT 1.26 [Value], ROUND(1.26,1) AS [RndValue]
UNION ALL
SELECT 1.27 [Value], ROUND(1.27,1) AS [RndValue]
UNION ALL
SELECT 1.28 [Value], ROUND(1.28,1) AS [RndValue]
UNION ALL
SELECT 1.29 [Value], ROUND(1.29,1) AS [RndValue]
UNION ALL
SELECT 1.30 [Value], ROUND(1.30,1) AS [RndValue]
UNION ALL
SELECT 1.31 [Value], ROUND(1.31,1) AS [RndValue]

Returned values:

Value	RndValues
1.23	1.20
1.24	1.20
1.25	1.30
1.26	1.30
1.27	1.30
1.28	1.30
1.29	1.30
1.30	1.30
1.31	1.30

You can "play" with modulo[^] too.

SQL

SELECT 1.23 [Value], ROUND(1.23,0) AS [RndValue], CONVERT(INT, 1.23 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.24 [Value], ROUND(1.24,0) AS [RndValue], CONVERT(INT, 1.24 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.25 [Value], ROUND(1.25,0) AS [RndValue], CONVERT(INT, 1.25 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.26 [Value], ROUND(1.26,0) AS [RndValue], CONVERT(INT, 1.26 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.27 [Value], ROUND(1.27,0) AS [RndValue], CONVERT(INT, 1.27 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.28 [Value], ROUND(1.28,0) AS [RndValue], CONVERT(INT, 1.28 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.29 [Value], ROUND(1.29,0) AS [RndValue], CONVERT(INT, 1.29 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.30 [Value], ROUND(1.30,0) AS [RndValue], CONVERT(INT, 1.30 * 10 % 10) AS [Modulo]
UNION ALL
SELECT 1.31 [Value], ROUND(1.31,0) AS [RndValue], CONVERT(INT, 1.31 * 10 % 10) AS [Modulo]

Value	RndValues	Modulo
1.23	1.00	2
1.24	1.00	2
1.25	1.00	2
1.26	1.00	2
1.27	1.00	2
1.28	1.00	2
1.29	1.00	3
1.30	1.00	3
1.31	1.00	3

... or ...
use CASE[^] expression ;)

SQL

USE A_TEST;  --my database

DECLARE @sql1 NVARCHAR(1000)
DECLARE @sql2 NVARCHAR(2000)

SET @sql1 = 'SELECT [GradeRng] = ' +
			'CASE  ' + 
			' WHEN [Grade] >=1 AND [Grade] <1.3 THEN ''1 to 1.29'' ' +
			' WHEN [Grade] >=1.3 AND [Grade] <1.7 THEN ''1.3 to 1.69'' ' +
			' WHEN [Grade] >=1.7 AND [Grade] <=2 THEN ''1.7 to 2'' ' +
			'END, [Weight] ' +
		'FROM Table_2 ' 
--EXEC (@sql1)

SET @sql2 = 'SELECT DT.[GradeRng], SUM(DT.[Weight]) AS [SumOfWeight] ' + 
			'FROM (' + @sql1 + ') AS DT ' +
			'GROUP BY DT.[GradeRng], DT.[Weight] ' +
			'ORDER BY  DT.[GradeRng], DT.[Weight] '
EXEC (@sql2)

results:

GradeRng	SumOfWeight
1 to 1.29	500
1.3 to 1.69	1000
1.7 to 2	1500