Aggregating groups of rows using SQL

  • Thread starter Thread starter Liddle Feesh
  • Start date Start date
L

Liddle Feesh

Dear Group,

I have a SQL coding problem, which I hope that you can help me with!

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 using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
SQL would be my preference to solve the programming task.

Many thanks in advance for your time and help.
 
SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY),
AVG(PRICE),
MIN(DATE)
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE
 
SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY),
AVG(PRICE),
MIN(DATE)
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE
Click to expand...

Many thanks for that - it seems to be exactly what I'm looking for.

Apologies for the double posting too; I've cancelled the second message and
forgot that I posted this one.

Thanks again.
 
SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE) as
[PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY [CUSTOMER],
[STOCK_CODE]

Although this is the wrong place to post this!

Cheers,

Greg Young
MVP - C#
 
hmm this showed up with no replies when I first saw it :-?

Greg Young said:
SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE)
as [PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY
[CUSTOMER], [STOCK_CODE]

Although this is the wrong place to post this!

Cheers,

Greg Young
MVP - C#

Liddle Feesh said:
Dear Group,

I have a SQL coding problem, which I hope that you can help me with!

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 using Visual Studio 2005 (.NET) and SQL Server 2005, although
generic SQL would be my preference to solve the programming task.

Many thanks in advance for your time and help.
Click to expand...
Click to expand...
 
Just one point...

Are you sure you want a straight average of stock price, rather than a
weighted average?

i.e. if 999 shares sell for $1000 each, and 1 share sells for $2, should
the average be $501 or should it be $999.002?

You may want something like this...

SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY) as TotalQuantity,
SUM(PRICE*quantity)/SUM(QUANTITY) as AvgPrice,
MIN(DATE) as FirstDate
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE
 
Back
Top