StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you're using SQL Server, after your query you can select the @@RowCount function (or if your result set might have more than 2 billion rows use the BIGROW_COUNT( ) function). This will return the number of rows selected by the previous statement or number of rows affected by an insert/update/delete statement.</p> <pre><code>SELECT my_table.my_col FROM my_table WHERE my_table.foo = 'bar' SELECT @@Rowcount </code></pre> <p>Or if you want to row count included in the result sent similar to Approach #2, you can use the the OVER clause (see <a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms189461.aspx</a><a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="noreferrer" title="http://msdn.microsoft.com/en-us/library/ms189461.aspx">1</a>). </p> <pre><code>SELECT my_table.my_col, count(*) OVER(PARTITION BY my_table.foo) AS 'Count' FROM my_table WHERE my_table.foo = 'bar' </code></pre> <p>Using the OVER clause will have much better performance than using a subquery to get the row count. Using the @@RowCount will have the best performance because the there won't be any query cost for the select @@RowCount statement</p> <p>Update in response to comment: The example I gave would give the # of rows in partition - defined in this case by "PARTITION BY my_table.foo". The value of the column in each row is the # of rows with the same value of my_table.foo. Since your example query had the clause "WHERE my_table.foo = 'bar'", all rows in the resultset will have the same value of my_table.foo and therefore the value in the column will be the same for all rows and equal (in this case) this the # of rows in the query. </p> <p>Here is a better/simpler example of how to include a column in each row that is the total # of rows in the resultset. Simply remove the optional Partition By clause.</p> <pre><code>SELECT my_table.my_col, count(*) OVER() AS 'Count' FROM my_table WHERE my_table.foo = 'bar' </code></pre>

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