CODEWITHSUNDEEP
mysqlsqlgroup-by
Brian Patterson
Brian Patterson

Reputation: 1625

MySQL Count Values (using group by?) based on 2 tables

Ok, its late, so this might be obvious, but I'm having some trouble with it.

I have 2 tables.

Filter
-

id
catId
value

All the values should be unique so this query gets all the values and their "id"s

select value as prodVal, id 
from Filter 
where catId = 7 group by prodVal

That query returns this ... 

id = 82, value = 'filter1'
id = 92, value = 'filter2'
etc.. etc...

I need to then take that list of "id"s and process it on the second table, which is

prodFilters
-
id
filterId
productId

So I need to count how many occurences of "FilterID" exists in "prodFilters" for every "ID" that was pulled up in the previous query ...

FOREACH (id from previous query) DO THIS ...

select count(*) from prodFilters 
where filterId = {value from last query} 
group by filterId

Is there any way to get this all accomplished through one query, rather than running the first query and then running multiple queries after it?

Any help is much appreciated.

Upvotes: 1

Views: 683

Answers (8)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

You should be able to use the IN clause 

SELECT filterid, COUNT(*)
FROM   prodfilters
WHERE  filterid IN (SELECT id 
                    from Filter 
                    WHERE  catid = 7 
                    group by prodVal)
GROUP  BY filterid

Upvotes: 3

Brian Patterson
Brian Patterson

Reputation: 1625

SELECT catId, COUNT(*)
FROM   prodFilters
WHERE  catId IN (SELECT id from Filter
                    WHERE  catId = 1 )
GROUP  BY catId

Upvotes: 1

jaychapani
jaychapani

Reputation: 1509

You can use like

With A as {

select value as prodVal, id 
from Filter 
where catId = 7 group by prodVal

}
select count(*) from prodFilters 
where filterId in {A.id} 
group by filterId

This works fine in oracle but not sure about MySQL

Mysql:

SELECT   COUNT (*)
    FROM prodfilters t
         JOIN
         (SELECT   VALUE AS prodval, ID
              FROM filter
             WHERE catid = 7
          GROUP BY prodval) ta ON ta.ID = t.filterid
GROUP BY filterid

Upvotes: 0

redmoon7777
redmoon7777

Reputation: 4526

Are you looking for something like this? 

select Filter.value as prodVal, Filter.id , count(filterId) from prodFilters, Filter where filterId IN (select id from Filter where catId = 7) group by filterId

Upvotes: 1

Mujassir Nasir
Mujassir Nasir

Reputation: 1730

You can use this query 

select COUNT(*) from  filter as f left outer join prodFilter as p on f.Id = p.filterId

Upvotes: 0

Tudor Olariu
Tudor Olariu

Reputation: 1328

    select count(filterid) 
from prodfilters where id in (select value as prodVal, id 
    from Filter 
    where catId = 7 group by prodVal) as temp 
    group by id

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Try this:

Select f.value as prodVal, f.id,  Count(pf.filterId) as 'Filter Count'
From Filter f
inner join prodFilters pf on f.id = pf.filterId
where f.catId = 7 
group by pf.filterId

Upvotes: 2

Maulik Vora
Maulik Vora

Reputation: 2584

select count(p.id) , p.filderId from prodFilters as p , filter as f
where p.filterId = f.id and f.catId = 7 
group by p.filterId

I think this would work, as each catId , value is unique field

Upvotes: 0

Related Questions