I am running a SELECT to perform a user search:
- has 4 LEFT JOINS
- WHERE query depends on user search criteria
- LIMIT 50

The data itself has categories and sub categories. While displaying the results in the middle of the screen, I want to show for these results how many are in each category, and allow user to click on category to drill down. Ebay is a good example of this:

A search on books returns 100+ results, LIMIT 50. I also need the following breakdown from the results:
Books Search
- Fiction (45)
- History (76)
- Non-Fiction (25)

The question:

Can I do this category breakdown count within the statement as above? Or do I need to have two statements, one SELECT for the results, and a second SELECT to return the category count?

This query will be performed a LOT so it has to be efficient.

Thanks so much!

if you are returning the record set to a scripting language like php, you could simply loop over the results for your counts

or, since you want to show the count as part of a link, then you are on a different page that does not use the detailed result set, so you would use a simpler count query

finally, look up the FOUND_ROWS function, you will need it too

Hi r937, thx for the reply but:
- Looping in script is not possible because I use a limit on results.
- This is all happening on one page. See ebay as an example.
- Found_Rows is used to count total rows (which I already do), it can't be used to count the categories.

if the result set consists of more than one row, then yes, you can loop in php

"see ebay as an example" doesn't quite do it for me

FOUND_ROWS gives you how many rows the query would have returned if you didn't use LIMIT

Let me clarify. I'm displaying this list to the user:
Book 1
Book 2
Book 4
...
Book 50

(limit=50, total from FOUND_ROWS=146)

Also looking to display this breakdown to user:
- Fiction (45)
- History (76)
- Non-Fiction (25)

Can I get both the book list AND the breakdown from the same SELECT statement?

only if you count them while you're printing them

Well, can't count while printing since I use a limit, and count must be on all.

Regardless, I already made this work nicely using two queries. The breakdown query is very fast so I believe I'm good for now...

Thx for help.

look up the FOUND_ROWS function in the mysql docs