Sql Comparing two tables: composite stored procedure

Hi,
I am trying to write a procedure in SQL which will composite values in a table. I have an increment list, which increments by 0.1, I need to average values so I produce composites for 0-1, 1-2 etc grouping by id.
The script below produces a table then a procedure, but I'm stuck on the syntax and get the error

Column 'Increment.increment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

The script will run on the master database.
The end result should look like the following:

thanks for any help

id inc_from inc_to comp_val
-
1a 0 1 4.6

1a 1 2 4

1b 0 1 3.9

1b 1 2 4.5

drop table increment

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0','6')
insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.2','4')
insert into increment(id,increment,value) values('1a','0.3','6')
insert into increment(id,increment,value) values('1a','0.4','2')
insert into increment(id,increment,value) values('1a','0.5','5')
insert into increment(id,increment,value) values('1a','0.6','8')
insert into increment(id,increment,value) values('1a','0.7','5')
insert into increment(id,increment,value) values('1a','0.8','1')
insert into increment(id,increment,value) values('1a','0.9','2')
insert into increment(id,increment,value) values('1a','1','3')
insert into increment(id,increment,value) values('1a','1.1','5')
insert into increment(id,increment,value) values('1a','1.2','4')
insert into increment(id,increment,value) values('1a','1.3','3')
insert into increment(id,increment,value) values('1a','1.4','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','1.6','1')
insert into increment(id,increment,value) values('1a','1.7','6')
insert into increment(id,increment,value) values('1a','1.8','6')
insert into increment(id,increment,value) values('1a','1.9','4')
insert into increment(id,increment,value) values('1a','2','2')
insert into increment(id,increment,value) values('1b','0','4')
insert into increment(id,increment,value) values('1b','0.1','7')
insert into increment(id,increment,value) values('1b','0.2','2')
insert into increment(id,increment,value) values('1b','0.3','1')
insert into increment(id,increment,value) values('1b','0.4','3')
insert into increment(id,increment,value) values('1b','0.5','5')
insert into increment(id,increment,value) values('1b','0.6','6')
insert into increment(id,increment,value) values('1b','0.7','4')
insert into increment(id,increment,value) values('1b','0.8','3')
insert into increment(id,increment,value) values('1b','0.9','6')
insert into increment(id,increment,value) values('1b','1','3')
insert into increment(id,increment,value) values('1b','1.1','5')
insert into increment(id,increment,value) values('1b','1.2','4')
insert into increment(id,increment,value) values('1b','1.3','3')
insert into increment(id,increment,value) values('1b','1.4','6')
insert into increment(id,increment,value) values('1b','1.5','8')
insert into increment(id,increment,value) values('1b','1.6','1')
insert into increment(id,increment,value) values('1b','1.7','6')
insert into increment(id,increment,value) values('1b','1.8','6')
insert into increment(id,increment,value) values('1b','1.9','4')
insert into increment(id,increment,value) values('1b','2','2')

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'usp_composite' AND type = 'FN')
DROP FUNCTION [dbo].[usp_composite]
GO

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
Select Id,
Ceiling(Increment) - 1 As Inc_From,
Ceiling(Increment) As Inc_To,
Avg(Value * 1.0) as comp_val
From Increment
Group By Id, Ceiling(Increment),Increment.increment
Order By Id, Ceiling(Increment)

You could do something like below:

select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to;

You are getting the error because your GROUP BY clause doesn't include the expressions that are in any of the aggregate functions. So it will work if you include ceiling(Increment) -1 also in the GROUP BY clause.

|||

Thanks for you help.

How do I modify the sql if the last depth value is correct, at the moment the sql returns 1 - 2 and not 1 - 1.95, see the code below with the create table.

drop table increment

create table increment(id varchar(10),increment float,value float)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_composite')

DROP PROCEDURE usp_composite

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to
go

|||

CELING will give the next closest integer value. So that value may not be in your table. You could check each max or min value against the values in your table. But it becomes complicated if you want to do it for each boundary value like 0 or 1 in 0-1, 1 or 2 in 1-2 and so on. And depending on how many possible values you have in your table it is going to be difficult. Can you answer some of the questions below?

1. What is the min/max range for the values in your Value column?

2. Do you want each range to be only the values in the column (like 0-1, 1-2, 2-3 and so on)?

If you always want only the actual min/max value within each ID as part of the min/max range buckets (inc_from , inc_to) then you can do something like below. The code will adjust the min/max values of each id based on the values in the table:

select id, inc_from, inc_to, avg(value)
from (
select i.id

/* adjust min if necessary based on the actual min value */
, case when ceiling(i.increment) - 1 < 0
then (case when 0 < i2.min_inc then i2.min_inc else 0 end)
else (case when ceiling(i.increment) - 1 < i2.min_inc then i2.min_inc else ceiling(i.increment) - 1 end)
end as inc_from
/* adjust min if necessary based on the actual max value */

, case when ceiling(i.increment) - 1 < 0
then (case when ceiling(i.increment) + 1 > i2.max_inc then i2.max_inc else ceiling(i.increment) + 1 end)
else (case when ceiling(i.increment) > i2.max_inc then i2.max_inc else ceiling(i.increment) end)
end as inc_to
, i.value
from increment as i
join (
select i1.id, min(i1.increment) as min_inc, max(i1.increment) as max_inc
from increment as i1
group by i1.id
) as i2
on i2.id = i.id
) as t
group by id, inc_from, inc_to;

|||

Many thanks, this works fine. I need to still average the data between 0-1,1-2,2-3 and so on but I need to display the correct increment values i.e. 1-1.95 and also if for example 0.5-1 rather than 0-1.

thanks

|||Put the increment value in your derived table. Now in you outer select, add Min(increment) and Max(increment). You get both the to and from values and the actual increments in your table.

Tuesday, March 20, 2012

composite stored procedure

No comments:

Post a Comment

Sql Comparing two tables

Blog Archive

About Me