Prev: CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Accumulate'.
Next: SQL Subquery/Temp Value
From: Farmer on 13 Nov 2009 17:17
Having this data set, is there any way to generate single summary row, with no UNION ALL, i.e. double query?

I tried grouping sets but can't figure it out.

Thank you for your help!

SELECT

keyID, val1, val2, val3, dt

FROM (

VALUES

(1, 10, .5 , 100, 1000)

,(2, 10, 1.5 , 10, 1000)

,(2, 10, .5 , 20, 1000)

) as d (keyID, val1, val2, val3, dt)

--GROUP BY GROUPING SETS((val1), (val2), (val3))

/*



keyID val1 val2 val3 dt

------------------------------------

1 10 0.5 100 1000

2 10 1.5 10 1000

2 10 0.5 20 1000

NULL 30 2.5 130 NULL



*/

UNION ALL



SELECT

null, SUM(val1), SUM(val2), SUM(val3), NULL

FROM (

VALUES

(1, 10, .5 , 100, 1000)

,(2, 10, 1.5 , 10, 1000)

,(2, 10, .5 , 20, 1000)

) as d (keyID, val1, val2, val3, dt)



From: Plamen Ratchev on 13 Nov 2009 18:05
Try this:

SELECT keyID, SUM(val1), SUM(val2), SUM(val3), dt
FROM (
VALUES(1, 10, .5 , 100, 1000)
,(2, 10, 1.5 , 10, 1000)
,(2, 10, .5 , 20, 1000)
) AS d (keyID, val1, val2, val3, dt)
GROUP BY GROUPING SETS((keyID, val1, val2, val3, dt), ());

--
Plamen Ratchev
http://www.SQLStudio.com
From: Farmer on 13 Nov 2009 20:36
Ah, that is what it is! I almost had it at one point. I was missing ", ()"
in SETS

Beautiful! You are good!

Thank you!

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:S5CdnVCHStS2e2DXnZ2dnUVZ_shi4p2d(a)speakeasy.net...
> Try this:
>
> SELECT keyID, SUM(val1), SUM(val2), SUM(val3), dt
> FROM (
> VALUES(1, 10, .5 , 100, 1000)
> ,(2, 10, 1.5 , 10, 1000)
> ,(2, 10, .5 , 20, 1000)
> ) AS d (keyID, val1, val2, val3, dt)
> GROUP BY GROUPING SETS((keyID, val1, val2, val3, dt), ());
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

 | 
Pages: 1
Prev: CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Accumulate'.
Next: SQL Subquery/Temp Value