Re: [sqlite] creating a summary table

[雷钦]

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