CODEWITHSUNDEEP
mysqlgroup-bycount
Dox
Dox

Reputation: 591

MySQL: group rows if column equals this or that

I have a simple table that kind of looks like this.

Restaurant      Category
-------------------------
Restaurant1     American
Restaurant1     French
Restaurant2     American
Restaurant2     French

Looking at that table, I can see that Restaurant1 has both American and French categories. I can also see that Restaurant2 has American and French categories. Seeing this, I could say that there are 2 posts that have both American and French categories.

Is it possible to translate that into MySQL? So, the query would have to be something like "get count(Restaurant) group by Restaurant if Category = American and Category = French". I know that wouldn't make any sense to MySQL, but I can't seem to figure out how to do this.

I noticed that if American and French were grouped if the post was equal, then I could easily group it by the Restaurant, but I'm not sure if there's a way to do that in MySQL.

For a real world example, I am trying to count how many posts there are in WordPress that have both a category with an ID of 22 and a category with an ID of 25. The query below should return 3. That's how many posts there are that have both the 22 and 25 categories.

SELECT COUNT(tr.object_id) AS post_count 
FROM wp_term_relationships AS tr
WHERE tr.term_taxonomy_id=25 AND tr.term_taxonomy_id=22

Has anyone run into this issue before?

Upvotes: 2

Views: 1174

Answers (1)

Nick
Nick

Reputation: 147266

You can use a query like this. It uses a derived table which sums how many distinct categories each restaurant has that match American or French. It then sums the number of restaurants that have 2 matching categories i.e. they are both American and French.

SELECT COUNT(Restaurant)
FROM (SELECT Restaurant, COUNT(DISTINCT Category) AS Am_or_Fr
      FROM restaurants
      WHERE Category IN ('American', 'French')
      GROUP BY Restaurant) r
WHERE Am_or_Fr = 2

Output (for your sample data)

2

A small demo on SQLFiddle

Update

For the query you added to your post, you would need to rewrite it as so:

SELECT COUNT(t.object_id) AS post_count 
FROM (SELECT tr.object_id, COUNT(DISTINCT tr.term_taxonomy_id) AS term_count
      FROM wp_term_relationships AS tr
      WHERE tr.term_taxonomy_id IN (22, 25)
      GROUP BY tr.object_id) t
WHERE term_count = 2

Upvotes: 3

Related Questions