Using Aggregate w/Create Temp Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Aggregate w/Create Temp Tables

Author

Topic

saulfeliz
Starting Member

14 Posts

Posted - 2012-06-25 : 11:44:45

SQL Helper Peeps,

I'm trying to create an aggregated temp table (results in about 16 million rows), from another massive table (about 300 million rows).

My statement to create the temp table looks like this:

Create Table #TempTable (
Var1 varchar(8),
VAR2 varchar (8),
Var3 varchar (16),
POS_Dollar_Sales numeric (16),
Quantity_Sold_to_Consumer numeric (16)
)

SELECT Distinct
Var1,
VAR2,
Var3 ,
POS_Dollar_Sales ,
Quantity_Sold_to_Consumer ,

From dbo.BS_POINT_OF_SALE

GROUP BY Var1, VAR2, Var3, POS_Dollar_Sales, Quantity_Sold_to_Consumer
ORDER BY Var1, VAR2, Var3

Go

What I want to do is aggregate the Dollar sales and units, and then call it something else. So a statement that looks something like this:

SUM (POS_Dollar_Sales) AS DOLLARS, SUM (Quantity_Sold_to_Consumer) AS UNITS

No matter where I put it, though, it gives me an error. Where do the aggregate and AS functions go for this to work?

GRACIAS!

Thanks!
Saul

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-25 : 11:50:17

inserrt #Temp
SELECT
Var1,
VAR2,
Var3 ,
SUM (POS_Dollar_Sales) AS DOLLARS,
SUM (Quantity_Sold_to_Consumer) AS UNITS
From dbo.BS_POINT_OF_SALE
GROUP BY Var1, VAR2, Var3

Note - get rid of the distinct and order by - they don't do anything.

Might be faster to do
SELECT
Var1,
VAR2,
Var3 ,
SUM (POS_Dollar_Sales) AS DOLLARS,
SUM (Quantity_Sold_to_Consumer) AS UNITS
into #temp
From dbo.BS_POINT_OF_SALE
GROUP BY Var1, VAR2, Var3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

saulfeliz
Starting Member

14 Posts

Posted - 2012-06-25 : 12:35:43

When I try that, I get:
"The definition for column 'Sum' must include a data type."

Here's what that part looks like now:

Create Table #TempTable (
Var1 varchar(8),
Var2 varchar (8),
Var3 varchar (16),
Sum (POS_Dollar_Sales) numeric (16) As Dollars,
Sum (Quantity_Sold_to_Consumer) numeric (16) As Units
)

Thanks!
Saul

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-25 : 14:51:17

quote:
Originally posted by saulfeliz

When I try that, I get:
"The definition for column 'Sum' must include a data type."

Here's what that part looks like now:

Create Table #TempTable (
Var1 varchar(8),
Var2 varchar (8),
Var3 varchar (16),
Sum (POS_Dollar_Sales) numeric (16) As Dollars,
Sum (Quantity_Sold_to_Consumer) numeric (16) As Units
)
Thanks!
Saul

You can't add aggregate functions to your CREATE TABLE statement like that, so you either want to:
1. CREATE the table then INSERT into the table
2. or, as Nigel showed, you can do an INSERT INTO that will create the table a load it in one statement.

saulfeliz
Starting Member

14 Posts

Posted - 2012-06-25 : 16:13:09

OK, that worked.
Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources