MySQL: need to get a count of rows

I'm trying to do a SQL query which also spits out a n开发者_如何学Goumber of qualifying rows from a different table, as a result.

Here's the current query, as inserted into sprintf:

SELECT a.* FROM websites AS a WHERE website_id = %d

Then, I have a query for retrieving the number of unique visits (from a table of visits) for a given site:

SELECT * FROM website_visits WHERE website_id = %d GROUP BY visitor_id

Now, is there any way to insert that query inside the first one, so that I can retrieve the number of unique visits, as part of the result of the first query? It would have to be something like this:

SELECT a.*, (SELECT * FROM website_visits AS b WHERE b.website_id = a.website_id GROUP BY visitor_id) AS unique_visits FROM websites AS a WHERE website_id = %d

But... as to the execution end, I'm stumped. Any help you can provide is appreciated!

---

To retrieve the row count, add SQL_CALC_FOUND_ROWS to the query, like:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name;

After this query, you can:

SELECT FOUND_ROWS();

To retrieve the row count.

(This retrieves the count in a cheap way; if you need the count as part of the rowset, you'll have to go with a subquery, like in your question.)

---

This gives you all your website records, and for each one, a count of how many unique visitor_ids there are for that site.

SELECT a.*, (
    SELECT COUNT(DISTINCT b.visitor_id)
    FROM website_visits AS b
    WHERE b.website_id = a.website_id) AS unique_visits
FROM websites AS a
WHERE website_id = %d