SQL Aggregation Query

[Liddle Feesh]

Dear Group,

I appreciate that you are all busy; however I have a SQL programming task
that I can't seem to get my head around at the moment. Any assistance will
be very warmly appreciated.

I have the following SQL view, which returns a Data Table, STOCK_TRADE, with
the following data..

CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 1000 535.50 10/05/2006
J. BLOGGS MSFT 2000 536.75 11/05/2006
J. BLOGGS GOOG 500 400.00 10/05/2006
J. BLOGGS GOOG 100 300.00 12/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006

My query is, I wish to aggregate rows which are for the same customer and
stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and the
FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:

CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 3000 536.13 10/05/2006
J. BLOGGS GOOG 600 350.00 10/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006

Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to GROUP
BY the STOCK_CODE and then just one line per customer for each stock that
they have.

Can anyone suggest the SQL - preferably without using cursors - to perform a
SELECT on the STOCK_TRADE table as shown in the top example to return the
aggregate Data Set in the bottom table?

I am attempting to produce a Crystal Report using Visual Studio 2005 (.NET)
and SQL Server 2005, although generic SQL would be my preference to solve
the programming task as it can then be incorporated into the data access
layer.

Many thanks in advance for your time and help.

X-Posted to: microsoft.public.dotnet.framework.adonet,
microsoft.public.dotnet.languages.vb.data,
microsoft.public.sqlserver.programming,
public.seagate.crystal-reports.programming

 

[John Smith]

You can try something like:

SELECT
CUSTOMER,
STOCK_CODE,
SUM(QUANTITY) AS Quantity,
AVG(PRICE) AS Price,
MIN([DATE]) AS [date]
FROM
STOCK_TRADE
GROUP BY
CUSTOMER,
STOCK_CODE

I think this will give you the results you are mentioned in question. Note
that the avg price is not a weighted average. Technically, I think you are
asking for the value that would be something like SUM(QTY*PRICE)/SUM(QTY)
Also, you want to make sure that the [quantity] field does not accept nulls,
as this would mess-up your sum.

Good luck with it. Hope this helps.

 

[Liddle Feesh]

You can try something like:

SELECT
CUSTOMER,
STOCK_CODE,
SUM(QUANTITY) AS Quantity,
AVG(PRICE) AS Price,
MIN([DATE]) AS [date]
FROM
STOCK_TRADE
GROUP BY
CUSTOMER,
STOCK_CODE

I think this will give you the results you are mentioned in question.
Note that the avg price is not a weighted average. Technically, I think
you are asking for the value that would be something like
SUM(QTY*PRICE)/SUM(QTY)
Also, you want to make sure that the [quantity] field does not accept
nulls, as this would mess-up your sum.

Good luck with it. Hope this helps.

Thanks for that, John. And also thanks for the suggestion of the more
technically correct AVG function. The database constraints will allow the
above code to work, and I appreciate your time - thanks a lot!