Inserting a record for each separate aggregate (solved)

[Thomas R. Hummel]

Hi,

As I wrote my message the solution came to me, so I thought I would
post anyway for others to see in case it was useful:

Here is some sample DDL for this question:

CREATE TABLE Source (
my_value INT NOT NULL )
GO
INSERT INTO Source VALUES (1)
INSERT INTO Source VALUES (2)
INSERT INTO Source VALUES (3)
GO
CREATE TABLE Destination (
value_type VARCHAR(10) NOT NULL,
value INT )
GO

I would like to fill the destination with a row for the COUNT, SUM,
MIN, and MAX. My own problem is of course much more complex than this,
but this is the basic stumbling block for me now. So, the rows that I
would expect to see in Destination are:

value_type value
---------- -----
COUNT 3
SUM 6
MIN 1
MAX 3

The solution that I came up with was to add a Value_Types table:

CREATE TABLE Value_Types (
value_type VARCHAR(10) NOT NULL )
GO
INSERT INTO Value_Types VALUES ('COUNT')
INSERT INTO Value_Types VALUES ('SUM')
INSERT INTO Value_Types VALUES ('MAX')
INSERT INTO Value_Types VALUES ('MIN')
GO

Now the SQL is pretty simple:

SELECT V.value_type,
CASE V.value_type
WHEN 'COUNT' THEN COUNT(*)
WHEN 'SUM' THEN SUM(S.my_value)
WHEN 'MAX' THEN MAX(S.my_value)
WHEN 'MIN' THEN MIN(S.my_value)
END
FROM Source S
INNER JOIN Value_Types V ON 1=1

-Tom.

P.S. - I know that I did not include primary or foreign keys in my DDL.
I'll leave it as an excercise to the reader to figure those out. I
think the code adequately explains the concept.

[Thomas R. Hummel]

The final SQL statement should have read:

SELECT V.value_type,
CASE V.value_type
WHEN 'COUNT' THEN COUNT(*)
WHEN 'SUM' THEN SUM(S.my_value)
WHEN 'MAX' THEN MAX(S.my_value)
WHEN 'MIN' THEN MIN(S.my_value)
END
FROM Source S
INNER JOIN Value_Types V ON 1=1
GROUP BY V.value_type

I left out the GROUP BY clause in my original post.

-Tom.

[Hugo Kornelis]

On 19 Apr 2005 09:03:39 -0700, Thomas R. Hummel wrote:

(snip)

FROM Source S
INNER JOIN Value_Types V ON 1=1
GROUP BY V.value_type

Hi Tom,

Since you're effectively doing a cross join, why not eliminate all doubt
and write it explicitly as such:

FROM Source S
CROSS JOIN Value_Types V
GROUP BY V.value_type

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Thomas R. Hummel]

Excellent point.

Thanks!
-Tom.