CODEWITHSUNDEEP
mysqlsqlselectgroup-byhaving
Soni Gunz
Soni Gunz

Reputation: 135

mysql count combination of non unique data in row

i have table like

+---+----+
|TID|DATA|
+---+----+
|1  |C   |
+---+----+
|1  |F   |
+---+----+
|1  |D   |
+---+----+
|1  |E   |
+---+----+
|2  |X   |
+---+----+
|2  |C   |
+---+----+
|2  |B   |
+---+----+
|2  |F   |
+---+----+
|3  |C   |
+---+----+

i want to count frequency from combination of data C and F group by their tid

so the count result for data C and F would be like = 2. (appear on tid 1 and 2)

this is my query

select count(data) from tabel where data like '%C%' and '%F%' group by tid

it didn't return the desired result

is it possible to query the result like that in mysql? or i need to manipulating it in program first? (i'm using vb net)

'UPDATE REQUIREMENT

i want to create the combination dynamically. is it possible to do that? i want to use while to iterate through the if statement. but it didn't return any result and gave me an error

here's my code. it's still error and i think the cause is because i can't use ALIAS COLUMN on IF CONDITIONAL

select count(*) from (
select kode_faktur,


if `DATA` like '%F%' and '%B%' then 1
else 0
end if

AS freq

FROM `table`
GROUP BY tid
HAVING freq = 2
) t

and this is what i wanna do, iterating the IF CONDITIONAL through WHILE

select count(*) from (
select kode_faktur,

while i<x
if `DATA` like '%i%' then 1
else 0
end if

AS freq

FROM `table`
GROUP BY tid
HAVING freq = x
) t

and this one is original code from @Aziz Shaikh and this code worked

SELECT COUNT(*) FROM (
SELECT tid,
SUM(CASE 
   WHEN `data` LIKE '%C%' THEN 1 
   WHEN `data` LIKE '%F%' THEN 1 
   ELSE 0 
   END
) AS freq
FROM `table`
GROUP BY tid
HAVING freq = 2
) t

personally i think this's to complicated in mysql. i choose to do this to saving my time instead of doing some code in program. the reason is because my program has already using too much connection and also proccessing big data. so i think mysql could be act like an alternative to my requirement

Upvotes: 1

Views: 196

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT SUM(cnt)
FROM(SELECT tid, COUNT(DISTINCT a.data) cnt
     FROM tabel a 
     WHERE a.data IN ('C', 'F')
     GROUP BY tid HAVING COUNT(DISTINCT a.data) = 2
   ) AS A

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

Try this:

SELECT COUNT(*) FROM (
 SELECT tid,
   SUM(CASE 
       WHEN `data` LIKE '%C%' THEN 1 
       WHEN `data` LIKE '%F%' THEN 1 
       ELSE 0 
       END
   ) AS freq
 FROM `table`
 GROUP BY tid
 HAVING freq = 2
) t

Upvotes: 1

xdazz
xdazz

Reputation: 160883

You should use OR instead of AND

select count(data) from tabel where data like '%C%' OR '%F%' group by tid

Upvotes: 1

Related Questions