please help me for this query, i have this data
customeid | param
1001 | A
1001 | B
1001 | C
1002 | B
1002 | A
1003 | A
1003 | B
1004 | A
i need to distinct customeid, i use this query but the result not correct
SELECT
COUNT(DISTINCT IF(param not in ('A'),1,0))
FROM
table
the output result
count
3 (from customeid 1001,1002,1003)
how to distinct customeid with if param not in A and ican't add where query
You use count if with distinct to judge that there is no problem, but there are issues, If the condition is met You need to return customeid, otherwise you need to return null
You can follow this query.
SELECT
COUNT(DISTINCT IF(param not in ('A'),customeid,NULL))
FROM
table
NOTE
COUNT won't accumulate when that value is null
select customeid,count(customeid)
from table
where param <>'A'
group by customeid
use case when and aggregate function group by
select customeid,count(*) as Count
from (
select (case when param!='A' then customeid else end) as customeid from your_table
) T group by customeid
SELECT DISTINCT COSTUMEID, COUNT(DISTINCT (PARAM))
FROM TABLE
WHERE PARAM NOT IN (('A'),1,0)
GROUP BY COSTUMEID
