Aggregate Counting

Hi,

How can I aggregate a top 5 count across two satellite tables?

e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.

Any help or pointers would be a great help!

Thanks.What's the actual problem with doing this?|||Use subquerys:select Customers.CustomerID,
isnull(OrdersSubquery.OrderCount, 0) as Orders,
isnull(DownloadsSubquery.DownloadCount, 0) as Downloads,
isnull(OrdersSubquery.OrderCount, 0) + isnull(DownloadsSubquery.DownloadCount, 0) as Total
from Customers
left outer join
(select CustomerID,
count(*) as OrderCount
from Orders
group by CustomerID) OrdersSubquery
on Customers.CustomerID = OrdersSubquery.CustomerID
left outer join
(select CustomerID,
count(*) as DownloadCount
from Downloads
group by CustomerID) DownloadsSubquery
on Customers.CustomerID = DownloadsSubquery.CustomerID
...or count distinct, if you have pkeys on Orders and Downloads:select Customers.CustomerID,
count(distinct Orders.OrderID) as Orders,
count(distinct DownloadsOrderID) as Downloads,
count(distinct Orders.OrderID) + count(distinct DownloadsOrderID) as Total
from Customers
left outer join Orders on Customers.CustomerID = Orders.CustomerID
left outer join Downloads on Customers.CustomerID = Downloads.CustomerID
group by Customers.CustomerID|||Thanks for the advice, the top selection was ideal for my purposes!

I just had to 'customise' it a little and add a few more table counts etc and all's well

:)

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.

Advise on querying a database daily

I am implementing an application that is looking into another System's database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.

What are my choices?

Thanks,

JasonWindows Scheduled Task is my preference. You could use SQL Tasks as well, I just don't like them.|||Lets say I do use the Windows Scheduled Task what type of project would it be since it does not have an interface?|||I usually just use VBS and then a COM callable .net component. The nice thing about SQL Tasks is that it will just run SQL.|||I have a question about SQL Tasks. I am running MSDE and I do have SQL Server Agent. I am using an Access project (adp) to manage my database. I do not see a utility to take advantage of SQL Server Agent/scheduling jobs. Do you have any advice on what I could use to generate the T-SQL so I can use SQL Server Agent?

Jason|||Sorry no idea, I'm an Enterprise Server user.