September 18, 2005 at 9:56 am
Hi Gurus,
Here is a simple question:
I have a table like this
StockId TransactionId Amount
MSFT 1 500
MSFT 1 2500
MSFT 2 1000
CSCO 1 2500
CSCO 1 2500
AMZN 1 4000
AMZN 2 5000
AMZN 2 4000
I want to get a result set with rows like these(basically group by stock id, avg amount for each of the transactionids) in a single sql statement.
StockId AvgAmountforTransaction id1 AvgAmountforTransactionid2
MSFT 1500 1000
CSCO 2500 0
AMZN 4000 4500
How do I do it in a single sql statement?
Thanks in advance ! This is my first post - pls. let me know if this is not the right forum to ask about sql server sql issues.
September 18, 2005 at 11:21 am
if the number of transactions isn't too great.
SELECT StockTransactions.StockID
,ISNULL(Tran1.AvgAmount,0) AS AvgAmountforTransactionid1 ,ISNULL(Tran2.AvgAmount,0) AS AvgAmountforTransactionid2
from ( /*All distinct StockIDs to join*/
select distinct StockID
from test.dbo.StockTransactions StockTransactions
) StockTransactions
left join /*Calculate transaction 1 */
(SELECT StockID,AVG(amount) AvgAmount
from test.dbo.StockTransactions
where TransactionID=1
group by StockID
)Tran1 on StockTransactions.StockID=Tran1.StockID
left join
(SELECT StockID,AVG(amount) AvgAmount
from test.dbo.StockTransactions
where TransactionID=2
group by StockID
)Tran2 on StockTransactions.StockID=Tran2.StockID
September 19, 2005 at 8:07 am
drop table StockTransaction
create table StockTransaction (
StockId varchar(10),
TransactionId int,
Amount money)
insert into StockTransaction values ('MSFT',1,500)
insert into StockTransaction values ('MSFT',1,2500)
insert into StockTransaction values ('MSFT',2,1000)
insert into StockTransaction values ('CSCO',1,2500)
insert into StockTransaction values ('CSCO',1,2500)
insert into StockTransaction values ('AMZN',1,4000)
insert into StockTransaction values ('AMZN',2,5000)
insert into StockTransaction values ('AMZN',2,4000)
select StockId,
avg(case TransactionId when 1 then Amount else null end) Transaction1Average,
avg(case TransactionId when 2 then Amount else null end) Transaction2Average
from StockTransaction
group by StockId
September 19, 2005 at 8:41 am
Thank you so much. This is amazingly concise.Awesome.
Thanks again guys.
Newbie
September 19, 2005 at 9:32 am
These examples do not cope with > 2 transactions, which I believe (from the other thread which you posted) is the requirement?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 19, 2005 at 10:26 am
The field is actually transactiontype which only has 2 values. So this should work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply