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 <[email protected]> wrote:
Subject: Re: [sqlite] partially excluding records
To: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
