Thanks everyone who helped me. This is what I ended up with:
SELECT key , COALESCE(SUM(CASE WHEN STATUS!='C' THEN 1 END) ,SUM(CASE WHEN STATUS='C' THEN 1 END)) CNT , COALESCE(MIN(CASE WHEN STATUS!='C' THEN STATUS END) || CASE WHEN COUNT(DISTINCT CASE WHEN STATUS!='C' THEN STATUS END)>1 THEN '+' END ,'C') STATUSES , COALESCE(MIN(CASE WHEN STATUS!='C' THEN ACTION END) || CASE WHEN COUNT(DISTINCT CASE WHEN STATUS!='C' THEN ACTION END)>1 THEN '+' END ,MIN(CASE WHEN STATUS='C' THEN ACTION END) || CASE WHEN COUNT(DISTINCT CASE WHEN STATUS='C' THEN ACTION END)>1 THEN '+' END) ACTIONS , COALESCE(SUM(CASE WHEN STATUS!='C' THEN VALUE END), SUM(CASE WHEN STATUS='C' THEN VALUE END)) VALUES FROM T1 GROUP BY T; I didn't include the Actions and values bit in my original question, but the final solution will does this with a half dozen different fields. (And I am already getting static because "MIN" isn't necessarily picking the filed value they would rather see. Anyway like Igor said, no where clause needed, which is a good thing since I need to fit this in to a sql statements that takes two pages to print in full, and I can't even remember what some of the where clauses are meant to do, let alone how I would combine them with new ones. David -------------------------------------------- On Fri, 2/21/14, Igor Tandetnik <i...@tandetnik.org> wrote: Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 3:25 PM On 2/21/2014 3:11 PM, David Bicking wrote: > But I am curious, wouldn't this yield a "Statuses" for key 2 of 'C+', when it should be 'O'? You could use the same technique there. The technique lets you know whether you have only C's, only non-C's, or a mix of the two, and act accordingly. For example, you could replace min(STATUS) with something like case when sum(STATUS != 'C') then min(case when STATUS='C' then 'ZZZ' else STATUS end) else 'C' end This returns the smallest of statuses other than C if any, and C otherwise. -- Igor Tandetnik _______________________________________________ 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