Adding filters to calculated fields SQL

Question

I have a table with these fields:

RefDate
Symbol
Timestamp
Sequence
Quantity
Price
SaleCondition
Pid
SubMkt

It has a list of stock trades with the date (RefDate), Symbol(AAPL, MSFT, DAVE, AMZN), quantity, price etc.

SaleCondition contains a list of codes that I need to use to find various things, e.g. "O" stands for "opening trade", "6" is "closing trade", and so on.

I need to calculate the opening price, closing price, min/max/avg price, the VWAP (volume weighted average price), the number of trades, the number of shares traded and the Volatility (calculated as the Max Price-Min Price/Last price).

I had to exclude many codes in SaleCondition to get the min/max/avg/VWAP, which I did correctly.

But I do not know how to pull the Opening Price and Closing Price into the query. I basically need to give a calculated field conditions (Select PRICE where SaleCondition="O" and PID="Q"). Obviously I can not do that because I need the WHERE clause to exclude many other codes.

Here's what I've come up with. The first one produces the correct min/max/average/vwap but the opening/closing prices are placeholders and the Shares/Trades is incorrect. The second two queries are the correct opening and closing prices.

dbGetQuery(nqdb, statement = 
    "select 
        RefDate, 
        Symbol, 
        Price as OpeningPrice, 
        Price as ClosingPrice, 
        Min(Price) as MinPrice, 
        Max(Price) as MaxPrice, 
        AVG(Price) as AvgPrice,
        Sum(Quantity*Price)/Sum(Quantity) as VWAP, 
        Count(Quantity) as Trades, 
        Sum(Quantity) as Shares, 
        (Max(Price)-Min(Price))/(Price) as PctRange 
    from trds 
    where 
        SaleCondition not like '%C%' and 
        SaleCondition not like '%G%' and 
        SaleCondition not like '%I%' and SaleCondition not like '%H%' and 
        SaleCondition not like '%M%' and SaleCondition not like '%N%' and 
        SaleCondition not like '%P%' and SaleCondition not like '%Q%' and 
        SaleCondition not like '%R%' and SaleCondition not like '%T%' and 
        SaleCondition not like '%U%' and SaleCondition not like '%V%' and 
        SaleCondition not like '%W%' and SaleCondition not like '%Z%' and 
        SaleCondition not like '%4%' and SaleCondition not like '%7%' and
        SaleCondition not like '%9%' 
    group by Symbol order by PctRange DESC")

dbGetQuery(nqdb, statement = 
    "select 
        RefDate, 
        Symbol, 
        Price as OpeningPrice 
    from trds 
    where SaleCondition like '%O%' and Pid='Q'")

dbGetQuery(nqdb, statement = 
    "select 
        RefDate, 
        Symbol, 
        Price as ClosingPrice 
    from trds 
    where SaleCondition like '%6%' and Pid='Q'")

About all those NOT LIKE for each character. Perhaps you can simplify that with a NOT GLOB, which allows to use ranges of characters. Beware that GLOB is case sensitive though. — LukStorms

GMB GMB · Accepted Answer · 2019-03-03T22:13:39

I think that you are looking for conditional aggregation. Instead of excluding records in the WHERE clause, you can implement the logic in aggregate functions. SInce you are not excluding records, you have access to all the data need to do all computations.

Here is an example for the opening and closing price, based on your original queries. You can add as many columns as necessary with other computations.

SELECT 
    RefDate, 
    Symbol, 
    MAX(CASE WHEN SaleCondition LIKE '%O%' AND Pid='Q' THEN Price END) as OpeningPrice,
    MAX(CASE WHEN SaleCondition LIKE '%6%' AND Pid='Q' THEN Price END) as ClosingPrice
FROM trds
GROUP BY 
    RefDate, 
    Symbol

Adding filters to calculated fields SQL

1 Answers