lundi 25 avril 2016

SQLite: Use of Aliases in Calculations

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