CODEWITHSUNDEEP
sqlite
showkey
showkey

Reputation: 246

how can combine the two sqlite statements into one statement?

 create  table  tmp  as select code,avg(how-low)/low  as vol  from quote group by code;
 select  avg(vol) from tmp

I create a new table with the first statement ,then select ave(vol) from tmp table.How can i combine the two sqlite statements into one statement?

Upvotes: 1

Views: 56

Answers (1)

CL.
CL.

Reputation: 180310

If you do not need the temporary table later, use a common table expression:

WITH tmp AS (SELECT avg(how-low)/low AS vol
             FROM quote
             GROUP BY code)
SELECT avg(vol)
FROM tmp

If you have an outdated SQLite version (older than 3.8.3), you could use a subquery instead:

SELECT avg(vol)
FROM (SELECT avg(how-low)/low AS vol
      FROM quote
      GROUP BY code)

Upvotes: 1

Related Questions