SQL join or PHP loop? or what?

Question

So i have two queries to give me the information i need and im trying to figure out the best way to get the result from them. i have a table of Holdings. using:

SELECT symbol, sum(shares) AS shares, sum(shares * price) AS cost 
FROM Transactions 
WHERE (action <>5) 
    AND date <= '2010-10-30' 
GROUP BY symbol HAVING sum(shares) > 0

which results in

AGNC    50.00     1390.0000
RSO     1517.00   9981.8600
T       265.00    6668.7500

I then have another query

SELECT close FROM History WHERE symbol = $symbol AND date = $date

which will return the closing price of that day.

T    24.40

i want to basically for a given date calculate value so sum(shares * close) for each symbol. but i dont know how to do that with out looping through each row in php. i was hoping there was a join or something i could do in sql to make the data return the way i want it

FYI you can use a number to compare a date value `date`<=20101030 but date is a reserved word, it shouldn't be a column name. — Popnoodles, Feb 16 '13 at 16:40
@popnoodles - but as so many people use date as a column name, it's one of the few reserved words that doesn't actually need backticks — Mark Baker, Feb 16 '13 at 16:51

score 0 · Accepted Answer · edited May 23 '17 at 12:29

0

I think you could do something similar to this: A select query selecting a select statement Put your second query literally in your first query. Not sure about exact syntax, but like:

SELECT 
    symbol, 
    sum(shares) AS shares, 
    sum(shares * price) AS cost, 
    sum(shares * close) as value 
FROM Transactions 
INNER JOIN History ON (symbol = $symbol AND date = $date)
WHERE (action <>5) 
    AND date <= '2010-10-30' 
GROUP BY symbol HAVING sum(shares) > 0

@popnoodles is right about your naming though. If you use date I'd think you'd need [date].

edited May 23 '17 at 12:29

Community

1
1

answered Feb 16 '13 at 16:44

Leeish

5,203
2
17
45

The problem with using another select query in there is that it only gives me that one symbol. As you can see in the first query example i have more than one symbol. i was hoping there might be a join of some sort i could do. other wise ill just have to do a loop in php – brandenwagner Feb 16 '13 at 16:49
1

Well, I think you could do something like `INNER JOIN History ON (symbol = $symbol AND date = $date)` and then add close to your select list and use it there. I think that will work. I am not sure if you can do that but I think you can. – Leeish Feb 16 '13 at 16:56
`INNER JOIN History ON (History.symbol = Transactions.symbol AND History.date = Transactions.date)`? – Popnoodles Feb 16 '13 at 17:15
THis is the way i got the data i wanted `SELECT Transactions.symbol, sum(shares) AS shares, sum(shares * price) AS cost, History.close as close, (shares * History.close) as value FROM Transactions INNER JOIN History ON (History.symbol = Transactions.symbol AND History.date ='2013-02-08') WHERE (action <>5) AND Transactions.date <= '2013-02-08' GROUP BY Transactions.symbol HAVING sum(shares) > 0` – brandenwagner Feb 16 '13 at 20:51

SQL join or PHP loop? or what?

1 Answers1