amuck-landowner

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

KuJoe

KuJoe

Well-Known Member
Verified Provider
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!
 
Last edited by a moderator:
drmike

drmike

100% Tier-1 Gogent
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.
 
D

dave

Member
Test query run on a securedragon vps.  :)

Code: 
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;
Code: 
+--------+-----+
| number | cnt |
+--------+-----+
|      4 |   2 |
|      5 |   1 |
+--------+-----+
 
KuJoe

KuJoe

Well-Known Member
Verified Provider
@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. :(

Code: 
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;
 
D

dave

Member
Here you go:

Code: 
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;
Code: 
+--------+
| number |
+--------+
|      4 |
|      5 |
+--------+
2 rows in set (0.00 sec)
 
KuJoe

KuJoe

Well-Known Member
Verified Provider
@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.
 
You must log in or register to reply here.
Top
amuck-landowner