| Author |
 Topic  |
|
|
saulfeliz
Starting Member
USA
14 Posts |
 Posted - 06/25/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/25/2012 : 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. |
Edited by - nigelrivett on 06/25/2012 11:51:18 |
 |
|
|
saulfeliz
Starting Member
USA
14 Posts |
Posted - 06/25/2012 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 06/25/2012 : 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
USA
14 Posts |
Posted - 06/25/2012 : 16:13:09
|
OK, that worked.
Thanks guys! |
|
|
| |
Topic |
|
|
|
Using Aggregate w/Create Temp Tables
