Average Value		Attribute	Quantity
AVG(CalculationValue)	ATTVALUE1	COUNT(CalculationValue)
AVG(CalculationValue)	ATTVALUE2	COUNT(CalculationValue)

quote:

---

Originally posted by clevesteve

I have to call the single-input stored procedure many times based on a list of inputs, then I want to aggregate/summarize the results of all the executions.

Each execution of the stored procedure yields a real (the calculation value -- I want to get the average of this), an nvarchar(10) (equivalent to the input value of the stored procedure... an identitiy value), and an nvarchar(4) (the attribute value I want to group my summary by.)

---

DECLARE @myidentity nvarchar(10)
CREATE TABLE #MyTempTable (
MYCALCULATIONVALUE real,
IDCOLUMN nvarchar(10),
MYATTRIBUTE nvarchar(4))

DECLARE cursorname CURSOR FOR SELECT idcolumn
FROM MyDatatable
WHERE <my where clauses>

OPEN cursorname

FETCH NEXT FROM cursorname INTO @myidentity

WHILE @@Fetch_Status = 0 BEGIN
INSERT INTO #MyTempTable EXEC myschema.mysproc @myidentity
FETCH NEXT FROM cursorname INTO @myidentity
END

CLOSE cursorname
DEALLOCATE cursorname

SELECT 
    ROUND(AVG(p.MYCALCULATIONVALUE),2) 'MyResult', 
    p.MYATTRIBUTE, 
    COUNT(p.MYCALCULATIONVALUE) 'Quantity', 
    DATEPART(ww,o.DATECOLUMN)
FROM #MyTempTable p, OtherTable o
WHERE p.IDCOLUMN = o.IDCOLUMN
GROUP BY p.MYATTRIBUTE, DATEPART(ww,o.DATECOLUMN)

DROP TABLE #MyTempTable