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
