[sqlite] Selecting total and not used in one query

[Luuk]

On 12-12-15 15:18, Cecil Westerhof wrote:
> ?I have the following query:
>      SELECT
>          (SELECT COUNT(*) FROM proverbs)                        AS Total
>      ,   (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used
>
> But I want something like:
> ?    SELECT
>          (SELECT COUNT(*) FROM proverbs)                        AS Total
>      ,   (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used
>      ,   (Total - Used)                                         AS Free
> ??
> ?But then I get:
> ?    no such column: Total
>
> It can be done, because I have it done in the past, but obviously a little
> different. :'-(
>
> ?Anybody knows how it can be done??
>


SELECT Total, Used, Total-Used as Free
FROM (SELECT (SELECT COUNT(*)
      FROM proverbs) AS Total ,
      (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used)