CODEWITHSUNDEEP
sqlsybase
richs
richs

Reputation: 4769

Sql Sybase row count of distinct set of rows

I have a table that contains a security_id and side (BUY or SELL) for every trade. I want to count the number of distinct security_id and side combinations.

So when i do the following I get a list of all symbol sides but i don't know how to get a total count?

select 
  security_id, side
from 
  trade
group by security_id, side

The result i want is 1 row which tells me how many distinct security_id, side i have in the table.

Also is there a way to determine which security_id(s) have entries for both sides (BUY and SELL).

Upvotes: 3

Views: 12548

Answers (2)

Thomas
Thomas

Reputation: 64645

Select Count(*)
From    (
        Select security_id, side
        From trade
        Group security_id, side
        ) As Z

To the second question:

Select security_id
From trade
Where side In('BUY','SELL')
Group security_id
Having Count( Distinct side ) = 2

Upvotes: 3

Kal
Kal

Reputation: 24910

Have you tried doing this?

select 
 security_id, side , count(*)
     from 
        trade
       group by security_id, side

Upvotes: 0

Related Questions