469,600 Members | 2,211 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

Inserting a record for each separate aggregate (solved)

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.

Jul 23 '05 #1
3 1677
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.

Jul 23 '05 #2
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)
Jul 23 '05 #3
Excellent point.

Thanks!
-Tom.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Joachim Klassen | last post: by
5 posts views Thread by Brian | last post: by
reply views Thread by Andy_Khosravi | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.