On 2013-02-18 17:02:53 +0000, Paul Sanderson wrote:
> n c
> 1 a
> 2 a
> 3 a
> 4 b
> 5 b
> 3 b
> 4 b
> 2 b
> 3 a
> 5 b
> 2 b
>
>
> 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
>
> n occurence unique
> 1 1 1
> 2 3 2
> 3 3 2
> 4 2 1
> 5 2 2
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
