Can an Alias be used in a calculated column in SQLite? I have the following query:
SELECT
dpTicker AS Ticker,
max(dpDate) AS Datum,
dpClose AS Price,
(SELECT
sum(div)
FROM Dividends
WHERE
divTicker = DP1.dpTicker
AND strftime('%Y', divDate) = strftime('%Y', date('now','-1 Year')))
AS sumDiv,
(SELECT
sum(div)
FROM Dividends
WHERE
divTicker = DP1.dpTicker
AND strftime('%Y', divDate) = strftime('%Y', date('now','-1 Year')))/dpClose*100
AS divYield
FROM DailyPrices AS DP1
GROUP BY dpTicker
ORDER BY divYield DESC;
As you can notice in the above query, the code for sumDiv is repeated twice. I have been searching how to use aliases in calculated column, however, my attempts have been unfruitful.
Direct use of the alias (sumDiv) in a calculation results in an error message. I have read that wrapping the alias around a SELECT statement should work:
(SELECT sumDiv)/dpClose AS divYield
This attempt results in the same error:
bash-3.2$ sqlite3 myShares < Queries/test.sql
Error: near line 1: no such column: sumDiv
Can aliases be used in calculation, or do I have to repeat the code twice?
Thanks in advance for any feedback, and helping to crawl up the SQLite learning curve.
Best regards,
GAM
Aucun commentaire:
Enregistrer un commentaire