Can we use the at sign in sqlite for a constant?-CodePudding

It is actually possible to use @ (the at sign) with sqlite to be able to use a calculated value as a constant in an other query ?

I am using a variable(a total) that i calculated previously to get an other variable (a proportion) over two time periods.

Total amout of sale Proportion of sale between the first semester and second semester.

I copy the first query to get the constant and i had the first query to the second.

CodePudding user response：

The answer is no BUT:-

This could possibly be done in a single query.

Consider this simple demo with hopefully easy to understand all-in-one queries:-

First the sales table:-

i.e. 2 columns semester and amount

10 rows in total so 1000 is the total amount 6 rows are S1 (amount is 600) so 60% 4 rows are S2 (amount is 400) so 40%

Created and populated using:-

CREATE TABLE IF NOT EXISTS sales (semester TEXT, amount REAL);
INSERT INTO sales VALUES('S1',100),('S1',100),('S1',100),('S1',100),('S1',100),('S1',100),('S2',100),('S2',100),('S2',100),('S2',100);

So you could use an all-in-one query such as:-

SELECT 
    (SELECT sum(amount) FROM sales) AS total, 
    (SELECT sum(amount) FROM sales WHERE semester = 'S1') AS s1total,
    ((SELECT sum(amount) FROM sales WHERE semester = 'S1') / (SELECT sum(amount) FROM sales)) * 100 AS s1prop,
    (SELECT sum(amount) FROM sales WHERE semester = 'S2') AS s2total,
    ((SELECT sum(amount) FROM sales WHERE semester = 'S2') / (SELECT sum(amount) FROM sales)) * 100 AS s2prop
;

This would result in

i.e. s1prop and s2prop the expected results (the other columns may be useful)

An alternative, using a CTE (Common Table Expressions) that does the same could be:-

WITH cte_total(total,s1total,s2total) AS (SELECT
    (SELECT sum(amount) FROM sales), 
    (SELECT sum(amount) FROM sales WHERE semester = 'S1'),
    (SELECT sum(amount) FROM sales WHERE semester = 'S2')
    )
SELECT total, s1total, (s1total / total) * 100 AS s1prop, s2total, (s2total / total) * 100 AS s2prop FROM cte_total;

you can have multiple CTE's and gather data from other tables or even being passed as parameters. They can be extremely useful and would even allow values to be accessed throughout.

e.g.

Here's an example where a 2nd cte is added (as the first cte) that mimics passing 3 dates (instead of the hard coded values ?'s could be coded and the parameters passed via parameter binding).

As the sales table has no date for the sale a literal value has been coded, this would be normally be the column with the sale date instead of WHERE '2023-01-01' /*<<<<< would be the column that holds the date */

the hard coded date has purposefully been used so result in the BETWEEN clause resulting in true.
if the date column did exist then WHERE criteria for the semester could then be by between the respective dates for the semester.

The example:-

WITH 
    dates AS (SELECT 
            '2023-01-01' /*<<<<< ? and can then be passed as bound parameter*/ AS startdate, 
            '2023-03-01' /*<<<<< ? and can then be passed as bound parameter*/ AS semester2_start, 
            '2023-05-30' /*<<<<< ? and can then be passed as bound parameter*/as enddate
    ),
        
    cte_total(total,s1total,s2total) AS (SELECT
    (SELECT sum(amount) FROM sales 
        WHERE '2023-01-01' /*<<<<< would be the column that holds the date */ 
            BETWEEN (SELECT startdate FROM dates) 
            AND (SELECT enddate FROM dates)), 
    (SELECT sum(amount) FROM sales WHERE semester = 'S1'),
    (SELECT sum(amount) FROM sales WHERE semester = 'S2')
    )

SELECT total, s1total, (s1total / total) * 100 AS s1prop, s2total, (s2total / total) * 100 AS s2prop FROM cte_total;

CodePudding user response：

O thank you for the deep answer. Interesting, i see, i will try something, it gives me some idea!