Geek asks and answers

MySQL: need to get the number of rows

I am trying to execute an SQL query, which as a result also spills out a number of qualification rows from another table.

Here's the current request inserted into sprintf:

CHOOSE a. * FROM websitesAS a WHERE website_id=% d

Then I have a request to get the number of unique visits (from the visit table) for this site:

SELECT * FROM website_visitsWHERE website_id=% d GROUP BYvisitor_id

Now, is there a way to insert this request inside the first so that I can get the number of unique visits as part of the result of the first request? It should be something like this:

SELECT a. *, (SELECT * FROM website_visitsAS b WHERE b. website_id= A. website_idGROUP BY visitor_id) AS unique_visits FROM websitesAS WHERE website_id=% D

But ... as for the end of the performance, I'm at a standstill. Any help you can provide is appreciated!

+3
source share
2 answers

To get the number of rows, add SQL_CALC_FOUND_ROWS to the query, for example:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name;

After this request, you can:

SELECT FOUND_ROWS();

To get the number of rows.

(This allows you to get an invoice in a cheap way, if you need an invoice as part of a rowset, you will have to go with a subquery, as in your question.)

+1
source

This gives you all the records of your site, and for each - the number of unique visitor_idfor this 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
+1
source

More articles:


All Articles