[sqlite] Selecting total and not used in one query

[R Smith]

or, more elegantly...

WITH PC(t, u, f) AS (
   SELECT 1, (P.used IS NOT NULL), (P.used IS NULL)
     FROM proverbs AS P
)
SELECT SUM(PC.t) AS Total, SUM(PC.u) AS Used, SUM(PC.f) AS Free
   FROM PC;




On 2015/12/12 4:18 PM, 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??
>