On Feb 23, 2013, at 12:07 AM, Paul Sanderson <[email protected]>
wrote:
> any ideas?
As mentioned, you will need two queries and union their respective result sets.
For example, assuming the following data set:
create table test( key, value );
insert
into test
( key, value )
values ( 'a', 1 ),
( 'a', 2 ),
( 'a', null ),
( 'b', 1 ),
( 'b', 1 ),
( 'b', 2 );
First, get the summary per key:
select key,
count( * ) as count,
count( distinct value ) as count_distinct,
count( value ) as count_value
from test
group by key
Then union it all with the grand total:
union all
select 'total' as key,
sum( count ) as count,
sum( count_distinct ) as count_distinct,
sum( count_value ) as count_value
from (
select key,
count( * ) as count,
count( distinct value ) as count_distinct,
count( value ) as count_value
from test
group by key
)
Resulting in:
key|count|count_distinct|count_value
a|3|2|2
b|3|2|3
total|6|4|5
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
