How to code a working self join in sql.(I get SYNTAX ERROR)

sql
#1

Hello,
I’ve been trying to code a self join for my Db and I get SYNTAX ERROR all the time.

I have a Table of stocks
Columns names:
stock -> stock name
trade -> short or long
time -> end time of trade
p_L-> profit / loss of trade

I want to code a self join query to sum (or count in another query) separately rofits and losses of all trades.

I other words : the sum ( or number of successful trades separately, and the sum ( or number of failedtrades separately.

SELECT P.p_l AS AS plus, L.p_l AS minus
FROM stocks
WHERE p.P_l>=0 and l.P_l<0
having TYPE = fb
ORDER BY o_time

what is wrong here? I can’t even get a list of the trades

#2

You’re not doing a self-join. You’re just trying to do a select, and the where cancels itself out.

You can try doing something like this.

SELECT SUM(CASE WHEN p.p_l >=0 THEN p.p_l ELSE 0 END) AS TotalProfit
     , SUM(CASE WHEN p.p_l < 0 THEN p.p_l ELSE 0 END) AS TotalLoss
  FROM stocks

If you want to break down the profit/losses by stock, you’d have to add additional fields to the query

SELECT stock
     , SUM(CASE WHEN p.p_l >=0 THEN p.p_l ELSE 0 END) AS TotalProfit
     , SUM(CASE WHEN p.p_l < 0 THEN p.p_l ELSE 0 END) AS TotalLoss
  FROM stocks
 GROUP BY stock
#3

Dave, which table does your table alias “p” refer to?

#4

I HATE when you catch my typos :smiley: Copy/paste from OP (which is what’s giving the syntax error, @erezvol).

The p alias isn’t needed

#5

Amazing. It Works,
How can I achieve your level of knowledge?
I tried to chane your code so that it counts the successful trades and faile trades by replacing sun with count, I got er.ror
I changed it to

SELECT stock
     , (CASE WHEN p.p_l >=0 THEN COUNT(p.p_l) ELSE 0 END) AS TotalProfit
     , (CASE WHEN p.p_l < 0 THEN COUNT(p.p_l) ELSE 0 END) AS TotalLoss
  FROM stocks
 GROUP BY stock

i GOT ERROR (as usual.
WHT???

#6

do some googling on table aliases

you are referring to a table alias “p” that doesn’t exist in your FROM clause

#7

also, COUNT belongs outside CASE, not inside