Re: [sqlite] Only see unused when there are unused records

[Paul Sanderson]

Or

SELECT count(*) AS Total,
  CASE
WHEN Sum(used = 'unused') > 0 THEN Sum(used = 'unused')
  END AS NotUsed
FROM quotes

There might be a more succinct way


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 9 May 2018 at 21:31, Igor Tandetnik  wrote:

> On 5/9/2018 4:19 PM, Cecil Westerhof wrote:
>
>> I have a table where I use 'unused' to signify that a record is not yet
>> used. I want to know the number of unused records (but only if there are
>> unused records) and the total number of records.
>>
>
> Something like this perhaps:
>
> select count(*) Total, sum(totalUsed = 'unused') NotUsed from quotes;
>
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Only see unused when there are unused records

[Igor Tandetnik]

On 5/9/2018 4:19 PM, Cecil Westerhof wrote:

I have a table where I use 'unused' to signify that a record is not yet
used. I want to know the number of unused records (but only if there are
unused records) and the total number of records.


Something like this perhaps:

select count(*) Total, sum(totalUsed = 'unused') NotUsed from quotes;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Only see unused when there are unused records

[José María Mateos]

Sorry, re-reading your question I realized my solution doesn't work: it would 
return 0 when there are unused, but you don't want to see them.

Cheers,

-- 
José María (Chema) Mateos
https://rinzewind.org/blog-es || https://rinzewind.org/blog-en
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Only see unused when there are unused records

[José María Mateos]

On Wed, May 9, 2018, at 16:19, Cecil Westerhof wrote:
> I have a table where I use 'unused' to signify that a record is not yet
> used. I want to know the number of unused records (but only if there are
> unused records) and the total number of records.
> 
> At the moment I implemented it like:
> SELECT   *
> FROM (
> SELECT   'Not used'  AS Type
> ,COUNT(*)AS NoUsed
> FROM quotes
> WHEREtotalUsed == 'unused'
> UNION ALL
> SELECT   'Total'  AS Type
> ,COUNT(*) AS NoUsed
> FROM quotes
> )
> WHERENoUsed > 0
> 
> Is this correct, or is there a better way?

Would this work?

SELECT COUNT(*) AS Total,
  SUM(CASE WHEN totalUsed = 'unused' THEN 1 END) AS NotUsed
FROM quotes;

Cheers,

-- 
José María (Chema) Mateos
https://rinzewind.org/blog-es || https://rinzewind.org/blog-en
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Only see unused when there are unused records

[Cecil Westerhof]

I have a table where I use 'unused' to signify that a record is not yet
used. I want to know the number of unused records (but only if there are
unused records) and the total number of records.

At the moment I implemented it like:
SELECT   *
FROM (
SELECT   'Not used'  AS Type
,COUNT(*)AS NoUsed
FROM quotes
WHEREtotalUsed == 'unused'
UNION ALL
SELECT   'Total'  AS Type
,COUNT(*) AS NoUsed
FROM quotes
)
WHERENoUsed > 0

Is this correct, or is there a better way?

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users