Grouping by Count

Question

Post reply

Grouping by Count

wrathyimp

SSC Eights!

Points: 847
More actions
April 15, 2008 at 6:41 am

#71832

Hi,
I have a Order Table that i need to view with count and grouping results,
this is the table:
TickerBuySellPrice
Item500 2020
Item500 2020
Item500 2020
Item1,88050
Item 1,88050
Item 1,820250
Item 1,820250
Item 1,820250
1,820 250
This is require result:
ProductPriceBidBid QtyAskAsk Qty
Item 2,020 19500
Item 2,000 23450
Item 1,980 25250
Item 1,960 25225
Item 1,940 36440
Item 1,920 29365
Item 1,900 775
Item 1,8802950
Item 1,86011250
Item 1,8406265
Item 1,82017250
How can I use Count, and then group by based on the buy sell columns
Bid = Count of Buy
Bid Qty = Buy
Ask = Count of Ask
Ask Qty = Sell
Please guide me as newbei in SQL (I am a networking guy)
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David-250683 Hall of Fame Points: 3452 More actions · Answer 1

Draft the CREATE TABLE and INSERT statements so I can test on my machine. Set your question up for success.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

...see the article at the URL in my signature for how to get the best help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wrathyimp SSC Eights! Points: 847 More actions · Answer 3

I have resolve it in my way, i don't know if its correct or wrong to to long process etc.

but the output is satisfactory. I have First created view called OB1 with

the following statement:

SELECT
Ticker,
Price,
Type,
COUNT(Price) AS CntPrice,
SUM(Qty) AS OpenQty
FROM OrderBook
GROUP BY OrShare, OrPrice, OrType

Then based on the above view(OB1) have have created 2nd view

with the following statement:

SELECT
TOP 100 PERCENT
Ticker,
Bid = CASE WHEN (Type = 'Buy') THEN CntPrice ELSE '' END,
BidQty = CASE WHEN (Type = 'Buy') THEN OpenQty ELSE '' END,
Ask = CASE WHEN (Type = 'Sell') THEN CntPrice ELSE ' ' END,
AskQty = CASE WHEN (Type = 'Sell') THEN OpenQty ELSE ' ' END,
Price,
FROM OB1
ORDER BY AskQty DESC, BidQty DESC

Seems working Okay, but not sure if its correct way, or it might reach to a point of failure

at a later point when the DB increases.

So thank you for your comments and guides.

Please reply if the above statements or procedures are correct.

Thank you.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

You just shouldn't put an ORDER BY in a view... what if someone wants it in a different order... lots of wasted clock cycles.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)