StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>With the help of Dems' comment (<a href="https://stackoverflow.com/users/53341/dems?tab=answers">hunt down and upvote him</a> somewhere :), I created this query. Notice that I removed the subquery, because it seemed unnecessary:</p> <pre><code>SELECT COUNT( DISTINCT p.host_id ) FROM plans p INNER JOIN hosts h ON h.id = p.host_id INNER JOIN prices ON prices.plan_id = p.id AND prices.price > 0 AND prices.price < 50 AND prices.term_duration = 1 WHERE p.published = 1 AND h.published = 1 </code></pre> <hr> <p>My original answer:</p> <p>To get the number of total row, you have to wrap the <code>GROUP BY</code> query into an outer <code>SELECT</code>:</p> <pre><code>SELECT COUNT(*) FROM ( SELECT NULL -- we are just counting, so we need no actual data -> a bit faster FROM plans p INNER JOIN hosts h ON h.id = p.host_id INNER JOIN prices ON prices.plan_id = p.id AND prices.price > 0 AND prices.price < 50 AND prices.term_duration = 1 WHERE p.published = 1 AND h.published = 1 GROUP BY p.host_id ) AS all_rows_without_data </code></pre> <p>Or you could use <code>SQL_CALC_FOUND_ROWS</code> + <code>FOUND_ROWS()</code></p> <p><a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows</a></p> <blockquote> <p>A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:</p> </blockquote> <p>First, simply select the required rows, but add <code>SQL_CALC_FOUND_ROWS</code>:</p> <pre><code>SELECT SQL_CALC_FOUND_ROWS p.host_id, p.rating_support, p.rating_tech, MAX(p.rating_overall) AS rating_overall, p.publish_rating, h.name, prices.price, prices.term_duration FROM plans p INNER JOIN hosts AS h ON h.id = p.host_id INNER JOIN prices ON prices.plan_id = p.id AND prices.price > 0 AND prices.price < 50 AND prices.term_duration = 1 WHERE p.published = 1 AND h.published = 1 GROUP BY p.host_id ORDER BY rating_overall desc LIMIT 0, 12; </code></pre> <p>Second, get the number of rows that would have been returned if there weren't a <code>LIMIT</code> statement in the first query:</p> <pre><code>SELECT FOUND_ROWS(); </code></pre> <p><em>Update</em>: <code>SQL_CALC_FOUND_ROWS</code> + <code>FOUND_ROWS()</code> doesn't seem very reliable, always returs zero for unknown reason (not just me: <a href="https://stackoverflow.com/questions/1020745/found-rows-keeps-returning-0">FOUND_ROWS() keeps returning 0</a> ):</p> <p><a href="http://sqlfiddle.com/#!2/7304d/8" rel="nofollow noreferrer">http://sqlfiddle.com/#!2/7304d/8</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload