Re: [sqlite] creating a summary table

2013-02-19 Thread Paul Sanderson 
That did the job - Thank You


On 18 February 2013 18:15, James K. Lowden  wrote:

> On Mon, 18 Feb 2013 17:02:53 +
> Paul Sanderson  wrote:
>
> > nc
> > 1a
> > 2a
> > 3a
> > 4b
> > 5b
> > 3b
> > 4b
> > 2b
> > 3a
> > 5b
> > 2b
> >
> >
> > I have a table as above
> >
> > I want to create a summary table that shows in the first column the
> > total number of occurrences of a value in the first column (n) and in
> > the second column for each value in n a count of the unique entries
> > in c
>
> Is this what you have in mind?
>
> sqlite> select n, count(*) as occurence, count(distinct c) as uniq
> from t group by n;
> n   occurence   uniq
> --  --  --
> 1   1   1
> 2   3   2
> 3   3   2
> 4   2   1
> 5   2   1
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a summary table

2013-02-18 Thread 雷钦 
On 2013-02-18 17:02:53 +, Paul Sanderson wrote:
> nc
> 1a
> 2a
> 3a
> 4b
> 5b
> 3b
> 4b
> 2b
> 3a
> 5b
> 2b
> 
> 
> I have a table as above
> 
> I want to create a summary table that shows in the first column the total
> number of occurrences of a value in the first column (n) and in the second
> column for each value in n a count of the unique entries in c
> 
> it should look like this
> 
> noccurenceunique
> 111
> 232
> 332
> 421
> 522

I think you can do this

SELECT n , sum(cou) AS oc, count(*) AS un 
FROM (SELECT n, c, count(*) AS cou 
FROM tablename 
GROUP BY n, c) 
GROUP BY n;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] creating a summary table

2013-02-18 Thread James K. Lowden 
On Mon, 18 Feb 2013 17:02:53 +
Paul Sanderson  wrote:

> nc
> 1a
> 2a
> 3a
> 4b
> 5b
> 3b
> 4b
> 2b
> 3a
> 5b
> 2b
> 
> 
> I have a table as above
> 
> I want to create a summary table that shows in the first column the
> total number of occurrences of a value in the first column (n) and in
> the second column for each value in n a count of the unique entries
> in c

Is this what you have in mind?  

sqlite> select n, count(*) as occurence, count(distinct c) as uniq 
from t group by n;
n   occurence   uniq  
--  --  --
1   1   1 
2   3   2 
3   3   2 
4   2   1 
5   2   1 

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

[sqlite] creating a summary table

2013-02-18 Thread Paul Sanderson 
nc
1a
2a
3a
4b
5b
3b
4b
2b
3a
5b
2b


I have a table as above

I want to create a summary table that shows in the first column the total
number of occurrences of a value in the first column (n) and in the second
column for each value in n a count of the unique entries in c

it should look like this

noccurenceunique
111
232
332
421
522

getting the first 2 columns is easy

select n, count(*) from table group by cat


but how do i create a combined table which also list s the unique counts on
c

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