Re: [sqlite] A total with a GROUP BY

[nomad]

On Thu Dec 01, 2016 at 07:57:06PM +0100, Cecil Westerhof wrote:
> At the moment I have the following code:
> SELECT totalUsed, COUNT(*) AS Count
> FROM tips
> GROUP BY totalUsed
> 
> This shows the total number of records for every value of totalUsed.
> Would it be possible to get the total number of records also. (Sum of
> all the Count's.)

Here is one way which I find easy to read:

    create table tips(
        totalUsed integer,
        item      varchar
    );

    insert into tips values(10,'item');
    insert into tips values(10,'item2');
    insert into tips values(12,'item3');
    insert into tips values(12,'item4');
    insert into tips values(12,'item5');

    with source(totalUsed,Counts) as (
        select
            totalUsed, COUNT(*)
        from
            tips
        group by
            totalUsed
    )
    select
        totalUsed,Counts
    from
        source
    union all select
        'Total:', sum(Counts)
    from
        source
    order by
        1
    ;

Result:

    totalUsed   Counts
    ----------  ----------
    10          2
    12          3
    Total:      5

Unfortunately it is not very efficient because SQLite executes
(expands?) the "source" select twice.

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