I have a sqlite query that returns something like the following with columns [letter, number]:
("a", 1)
("a", 2)
("b", 3)
("c", 3)
I want to retrieve the number column as 0 if the letter is distinct. How is it done?
Expected output:
("a", 1)
("a", 2)
("b", 0)
("c", 0)
SELECT tba.mychar
-- if tbu.mychar is null, then the letter is not unique
-- when it happens, the letter is not "unique" thus use the number column
-- else use zero for "unique" letters
, CASE WHEN tbu.mychar IS NULL THEN tba.mynum ELSE 0 END AS newnum
FROM mytab tba
LEFT JOIN (
-- this subquery only returns the letters that don't repeat
SELECT mychar
FROM mytab
GROUP BY mychar
HAVING COUNT(*) = 1
) AS tbu ON tba.mychar=tbu.mychar
You can use a sub-query:
select t1.col1,
case when t2.cnt > 1 then t1.col2 else 0 end col2
from table1 t1
left join
(
select count(*) as cnt, col1
from table1
group by col1
) t2
on t1.col1 = t2.col1
How about (SQL Fiddle):
SELECT Q.letter,
CASE WHEN (SELECT COUNT(*) FROM (query) QQ WHERE QQ.letter = Q.letter) = 1 THEN 0
ELSE Q.number
END AS number
FROM (query) Q
Note, replace "query" with the query that generates your first result.
It's possible to do this using a UNION ALL of 2 separate statements (one for repeated letters and one for letters that only occur once):
SELECT letter, number
FROM tableName
WHERE letter IN (
SELECT letter
FROM tableName
GROUP BY letter
HAVING COUNT(1) > 1
)
UNION ALL
SELECT letter, 0
FROM tableName
WHERE letter IN (
SELECT letter
FROM tableName
GROUP BY letter
HAVING COUNT(1) = 1
)
UNION ALL would be more efficient.