Aggregate - Sum with group by

Hi,

I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)

ID Unit Price Quantity State 1 $2.19 500 AZ 2 $29.99 33 WA 3 $1000.00 1 WA 4 $1.20 7 WA

When i run the aggregate i want the output to be grouped by state

Total Price Quantity Sold State $2.19 500 AZ $1031.19 41 WA

Hope the values are correct

Martin

Add the aggregate to your data flow, and connect an input. Open the properties and check the Unit Price, Quantity, and State on the available input columns. In the grid at the bottom, make sure the State has the Operation set to "Group By", and Operation to "Sum" for the Unit Price and Quantity columns. That should do it.|||In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?|||

Martin Perkins wrote:

In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?

That's strange. What's the data type of the state column? Have you installed service pack 1 at least?|||

The State property is Nvarchar(max) Just installing service pack 2 for SQL Server 2005

|||

It was the length of the state column. If i convert the column from Nvarchar(max) to Nvarchar(20) i can then group by.

Thanks for help

Martin

|||

SSIS treats NVARCHAR(MAX) as BLOB type, and does not support grouping by blobs. You need to convert it to fixed-lenght type, like NVARCHAR(2).

You can do it either

1) at the source - NVARCHAR(MAX) is very inefficient method of storing 2-character data, if you can change the source, I suggest doing it

2) case to fixed-lenght type in the SQL query

3) use SSIS to create another column of fixed lenght (I'm not sure if type convertion transform does it, if not then try derived column transform)

Hope this help,

Michael.

Aggragate transform with "WHERE" condition

Hello, I am new in SSIS.

I have to Load a table with aggregate data, I can do it using Aggragate transformation, but other the "GROUP BY" I need to make a filter on input records with "WHERE" condition.

In TSQL it should be:

SELECT col1, SUM(col2) FROM T

GROUP BY col1

WHERE col3 = value.

But in SSIS ?

How can I do it?

Thank

If it is in the Where clause and not Having, I would just place the Where in the Data Source that feeds into the Aggregate Transformation.|||

I need more details... I dont know if I understood...

You can add the where condition in your SQL statment in the datasource, or you can use the ConditionSplit transform to filter the data... you more other options...

give more details...

helped?

regards

|||Use a conditional split before the aggregation to filter your data.|||

csi_hugh wrote:

If it is in the Where clause and not Having, I would just place the Where in the Data Source that feeds into the Aggregate Transformation.

So would I. If you have the option to, always filter the data at source rather than use a Conditional Split.

-Jamie