-------------------------------------------- On Fri, 2/21/14, RSmith <rsm...@rsweb.co.za> wrote:
Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 1:34 PM On 2014/02/21 20:23, David Bicking wrote: > I have a table like > > SELECT * FROM T1; > Key Status > 1 O > 1 O > 2 O > 2 C > 3 C > 3 C > 4 O > 4 P > > > Now, I need to consolidate that data. > > SELECT Key, COUNT(STATUS) Cnt > , MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END Statuses > FROM T1 > WHERE ... > GROUP BY KEY; > > Key Cnt Statuses > 1 2 O > 2 1 C > 4 2 O+ > >> The complication is that if a given key has any non-C value, the C values >> are to be excluded. If there are only C values, they are to be included. >> How can I state the WHERE clause to do that? >This last statement contradicts your example completely. You say: "if a given >key >has any non-C value, the C values are to be excluded" >But looking at the table the Key-value 2 has one non-C value, yet it is >included and showing the C. >You then say: "If there are only C values, they are to be included" >But Key "3" clearly contains only C values, yet they are explicitly excluded >from the result list >If you could kindly fix either the statement or the example so we know which >is accurate, then will gladly try to solve the >WHERE riddle for you! Sorry, stupid typos on my part. Key Cnt Statuses 1 2 O 2 1 O 3 2 C 4 2 O+ _______________________________________________ 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
