CODEWITHSUNDEEP
mysqlselectcountsubquery
CheeseFlavored
CheeseFlavored

Reputation: 2112

MySQL Combining two queries to get two counts

My table contains items that each have a category number. I have a nice query that counts now many items it finds in each category. It looks like this if I was searching for the word "cheese"

$result=mysql_query("SELECT category, COUNT(*) AS 'itemcount' 
                     FROM mytable 
                     WHERE title like '%cheese%' 
                     GROUP BY category 
                     order by itemcount")

Now I have a list of all category numbers that have "cheese" and a count of how many are in each category. I also use second query to count the TOTAL number of "cheese" items. Rather than doing a second query, is there a way to add all the itemcounts to get the total...So only one query is needed?

`

Upvotes: 0

Views: 28

Answers (1)

Nick
Nick

Reputation: 147166

You can add a WITH ROLLUP to your GROUP BY clause, and this will give you an extra row at the end of your table (with a NULL value for category) which has the total number of cheese items e.g.

$result=mysql_query("SELECT category, COUNT(*) AS 'itemcount' 
                     FROM mytable 
                     WHERE title like '%cheese%' 
                     GROUP BY category WITH ROLLUP 
                     ORDER BY itemcount");

Example output:

category    itemcount
restaurant          5
hotel               2
shop               11
NULL               18

Upvotes: 2

Related Questions