PHP/MySQL Help Needed - Count rows with the same value in one column and checking for value in anoth

[KuJoe]

I hope I worded the title correctly, I'm drawing a blank right now on the correct terms so I'll describe what I'm trying to do instead.

I want to return only the rows in a table that all have the same value in one column but only if they all have a specific value in another column.

So here's an example of the 2 columns in the table:


Number | Letter
1 | a
1 | a
2 | a
2 | b
3 | c
3 | b
4 | c
4 | c
5 | c

So I want to return all of the Numbers that have only C for all of their Letters and count the number of rows for that Number.

Number 1 has no Cs so it will not be returned.

Number 2 has no Cs so it will not be returned.

Number 3 has 1 C but also 1 B so it will not be returned.

Number 4 has all Cs so it will be returned.

Number 5 has all Cs (even though it's only 1) so it will be returned.

I was thinking of using GROUP BY in the query, but then I started reading about CONCAT and explode() so I'm even more lost than before.

Any assistance is appreciated. Thanks!

 

[drmike]

Well this isn't elegant.  A DBA would snap an answer out in a quicky


SELECT DISTINCT(number)
FROM joe
WHERE number IN (SELECT number FROM joe WHERE letter = 'c')
AND number NOT IN (SELECT number FROM joe WHERE letter <> 'c');
That will give you the records only containing the letter 'c'.

As far as getting the column counts for the two numbers... You could in PHP take the query results and loop them fetching the result total for the individual numbers.   Not elegant, but simple and done.

SQL requires a sub query to do this and well, been eons since I fussed with that.  Gave it some attempts and said fark it. Next.

 

[Mr. Obvious]

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php

Something like this give you a good start?

 

[dave]

Test query run on a securedragon vps. 

select
  a.number,
  a.cnt
from
  (select
    number,
    count(*) as 'cnt'
  from
    test
  where
    letter = 'c'
  group by
    number) as a
inner join
  (select
    number,
    count(*) as 'cnt'
  from
    test
  group by
    number) as b
on
  a.number = b.number
and 
  a.cnt = b.cnt;

+--------+-----+
| number | cnt |
+--------+-----+
|      4 |   2 |
|      5 |   1 |
+--------+-----+

 

[KuJoe]

@dave Can you rewrite the query to remove the count and just show the number? I thought it would be something like this but it adds about 100 incorrect records to the output.

select
  a.number
from
  (select
    number
  from
    test
  where
    letter = 'c'
  group by
    number) as a
inner join
  (select
    number
  from
    test
  group by
    number) as b
on
  a.number = b.number;

 

[dave]

Here you go:

select
  a.number
from
  (select
    number,
    count(*) as 'cnt'
  from
    test
  where
    letter = 'c'
  group by
    number) as a
inner join
  (select
    number,
    count(*) as 'cnt'
  from
    test
  group by
    number) as b
on
  a.number = b.number
and 
  a.cnt = b.cnt;

+--------+
| number |
+--------+
|      4 |
|      5 |
+--------+
2 rows in set (0.00 sec)

 

[KuJoe]

@dave Doh! I feel so dumb now. I really need to sit down and learn me some MySQL.

Thanks for your help! I was able to get the report and script working properly.

 

[dave]

No problem, glad I could help.