Re: [sqlite] partially excluding records
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 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
Re: [sqlite] partially excluding records
Hello I would simply inner join, then filtering. Best Regards. On Fri, Feb 21, 2014 at 12:11 PM, David Bicking wrote: > > > On Fri, 2/21/14, Igor Tandetnik wrote: > > Subject: Re: [sqlite] partially excluding records > To: sqlite-users@sqlite.org > Date: Friday, February 21, 2014, 2:58 PM > > On 2/21/2014 1:23 PM, > David Bicking wrote: > >> SELECT Key, COUNT(STATUS) Cnt > >> , MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END > Statuses > >> FROM T1 > >> WHERE ... > >> GROUP BY KEY; > >> > > > You might be looking for something like this: > > select key, sum(STATUS != 'C') + (case when sum(STATUS != 'C') = 0 then > sum(STATUS > > 'C') else 0 end) Cnt, ... > > > No special WHERE clause needed. > > *** found another typo in my example, that should have been WHEN > COUNT(DISTINCT STATUS)>1 *** > > *** Annoying that they make up their minds that they want it to work like > this today, then end the meeting with... and you can have this done by > Monday, right? I need to calm down. *** > > Anyway, there are other fields and messy CASE statements that probably > wouldn't work with this solution. > > But I am curious, wouldn't this yield a "Statuses" for key 2 of 'C+', when > it should be 'O'? > > Thanks, > David > > ___ > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partially excluding records
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
Re: [sqlite] partially excluding records
On Fri, 2/21/14, Igor Tandetnik wrote: Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 2:58 PM On 2/21/2014 1:23 PM, David Bicking wrote: >> SELECT Key, COUNT(STATUS) Cnt >> , MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END Statuses >> FROM T1 >> WHERE ... >> GROUP BY KEY; >> > You might be looking for something like this: > select key, sum(STATUS != 'C') + (case when sum(STATUS != 'C') = 0 then > sum(STATUS > 'C') else 0 end) Cnt, ... > No special WHERE clause needed. *** found another typo in my example, that should have been WHEN COUNT(DISTINCT STATUS)>1 *** *** Annoying that they make up their minds that they want it to work like this today, then end the meeting with... and you can have this done by Monday, right? I need to calm down. *** Anyway, there are other fields and messy CASE statements that probably wouldn't work with this solution. But I am curious, wouldn't this yield a "Statuses" for key 2 of 'C+', when it should be 'O'? Thanks, David ___ 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
Re: [sqlite] partially excluding records
On Fri, 2/21/14, Clemens Ladisch wrote: Subject: Re: [sqlite] partially excluding records To: sqlite-users@sqlite.org Date: Friday, February 21, 2014, 1:38 PM David Bicking wrote: >> The complication is that if a given key has any non-C value, the C values >> are to be excluded. > First, just exclude all C values: > ... WHERE Status <> 'C' ... > If there are only C values, they are to be included. >Then do the same query again, but with the all-C keys: > ... > UNION ALL > SELECT ... > WHERE Key NOT IN (SELECT Key FROM T1 WHERE Status <> 'C') > ... Reality is that the "Key" is a combination of 3 or 4 fields. I suppose I can concatenate the fields, but that seems overly ugly. And the select and existing part of the where clause are complicated, so if I can avoid repeating all of that, I'd rather avoid doing it as a union query. But if needs must, I will go with this idea. Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partially excluding records
On 2/21/2014 1:23 PM, David Bicking wrote: 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? You might be looking for something like this: select key, sum(STATUS != 'C') + (case when sum(STATUS != 'C') = 0 then sum(STATUS = 'C') else 0 end) Cnt, ... No special WHERE clause needed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partially excluding records
On Fri, 2/21/14, RSmith 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 2O > 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
Re: [sqlite] partially excluding records
David Bicking wrote: > The complication is that if a given key has any non-C value, the C values are > to be excluded. First, just exclude all C values: ... WHERE Status <> 'C' ... > If there are only C values, they are to be included. Then do the same query again, but with the all-C keys: ... UNION ALL SELECT ... WHERE Key NOT IN (SELECT Key FROM T1 WHERE Status <> 'C') ... Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] partially excluding records
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! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] partially excluding records
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? Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
