SQL statement question
-
Hi Gurus,
Here is a simple question:
I have a table with columns as
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
AMZN 4000 4500
How do I do it?
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.
-
Are there always two transaction IDs for each StockID, or is the number variable (in which case, you want a variable number of columns)?
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 -
Number is variable
-
This cannot be done in a single SQL statement. If you definitely need it in this format, you'll need a chunk of code (examples of which appear elsewhere on this site - you're definitely not the first to want this).
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 -
It can be done. http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=220954
Please don't cross post.
I wasn't born stupid - I had to study.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply