beefed up documentation for count(), [ticket:1465]

1 files changed, 49 insertions, 1 deletions

diff --git a/doc/build/ormtutorial.rst b/doc/build/ormtutorial.rst
index acdbba149..c10d457f1 100644
--- a/doc/build/ormtutorial.rst
+++ b/doc/build/ormtutorial.rst
@@ -567,6 +567,54 @@ To use an entirely string-based statement, using ``from_statement()``; just ensu
     ['ed']
     {stop}[<User('ed','Ed Jones', 'f8s7ccs')>]
 
+Counting
+--------
+
+``Query`` includes a convenience method for counting called ``count()``:
+
+.. sourcecode:: python+sql
+
+    {sql}>>> session.query(User).filter(User.name.like('%ed')).count()
+    SELECT count(1) AS count_1 
+    FROM users 
+    WHERE users.name LIKE ?
+    ['%ed']
+    {stop}2
+    
+The ``count()`` method is used to determine how many rows the SQL statement would return, and is mainly intended to return a simple count of a single type of entity, in this case ``User``.   For more complicated sets of columns or entities where the "thing to be counted" needs to be indicated more specifically, ``count()`` is probably not what you want.  Below, a query for individual columns does return the expected result:
+
+.. sourcecode:: python+sql
+
+    {sql}>>> session.query(User.id, User.name).filter(User.name.like('%ed')).count()
+    SELECT count(1) AS count_1 
+    FROM (SELECT users.id AS users_id, users.name AS users_name 
+    FROM users 
+    WHERE users.name LIKE ?) AS anon_1
+    ['%ed']
+    {stop}2
+
+...but if you look at the generated SQL, SQLAlchemy saw that we were placing individual column expressions and decided to wrap whatever it was we were doing in a subquery, so as to be assured that it returns the "number of rows".   This defensive behavior is not really needed here and in other cases is not what we want at all, such as if we wanted a grouping of counts per name:
+
+.. sourcecode:: python+sql
+
+    {sql}>>> session.query(User.name).group_by(User.name).count()
+    SELECT count(1) AS count_1 
+    FROM (SELECT users.name AS users_name 
+    FROM users GROUP BY users.name) AS anon_1
+    []
+    {stop}4
+
+We don't want the number ``4``, we wanted some rows back.   So for detailed queries where you need to count something specific, use the ``func.count()`` function as a column expression:
+
+.. sourcecode:: python+sql
+    
+    >>> from sqlalchemy import func
+    {sql}>>> session.query(func.count(User.name), User.name).group_by(User.name).all()
+    SELECT count(users.name) AS count_1, users.name AS users_name 
+    FROM users GROUP BY users.name
+    {stop}[]
+    [(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]
+
 Building a Relation
 ====================
 
@@ -824,7 +872,7 @@ The ``Query`` is suitable for generating statements which can be used as subquer
         (SELECT user_id, count(*) AS address_count FROM addresses GROUP BY user_id) AS adr_count
         ON users.id=adr_count.user_id
 
-Using the ``Query``, we build a statement like this from the inside out.  The ``statement`` accessor returns a SQL expression representing the statement generated by a particular ``Query`` - this is an instance of a ``select()`` construct, which are described in :ref:`sql`::
+Using the ``Query``, we build a statement like this from the inside out.  The ``statement`` accessor returns a SQL expression representing the statement generated by a particular ``Query`` - this is an instance of a ``select()`` construct, which are described in :ref:`sqlexpression_toplevel`::
 
     >>> from sqlalchemy.sql import func
     >>> stmt = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()