title: Column must appear in the GROUP BY clause or be used in an aggregate function
body: <p>I'm updating a Qt software, to make it compatible with both SQLite and PostgreSQL.
  I have a C++ method that is used to count elements of a given table with given clauses.</p>
  
  <p>In SQLite, the following worked and gave me a number N (the count).</p>
  
  <pre><code>SELECT COUNT(*) FROM table_a 
  INNER JOIN table_b AS
  ON table_b.fk_table_a = table_a.id
  WHERE table_a.start_date_time &lt;&gt; 0
  ORDER BY table_a.creation_date_time DESC
  </code></pre>
  
  <p>With PostgreSQL (I'm using 9.3), I have the following error :</p>
  
  <blockquote>
    <p>ERROR:  column "table_a.creation_date_time" must appear in the
    GROUP BY clause or be used in an aggregate function
    LINE 5: ORDER BY
    table_a.creation_date_time DESC</p>
  </blockquote>
  
  <p>If I add, GROUP BY table_a.creation_date_time, it gives me a table with N rows.
  I've read a lot of stuff about how different DBMS allow you to omit columns in the GROUP BY clause. Now, I'm just confused.</p>
  
  <hr>
  
  <p>For those who are curious, the C++ method is:</p>
  
  <pre><code>static int count(const QString &amp;table, const QString &amp;clauses = QString(""))
  {
      int success = -1;
  
      if (!table.isEmpty())
      {
          QString statement = QString("SELECT COUNT(*) FROM ");
          statement.append(table);
          if (!clauses.isEmpty())
          {
              statement.append(" ").append(clauses)   ;
          }
          QSqlQuery query;
          if(!query.exec(statement))
          {
              qWarning() &lt;&lt; query.lastError();
              qWarning() &lt;&lt; statement;
          }
          else
          {
              if (query.isActive() &amp;&amp; query.isSelect() &amp;&amp; query.first())
              {
                  bool ok = false;
                  success = query.value(0).toInt(&amp;ok);
                  if (ok == false)
                  {
                      success = -1;
                      return success;
                  }
              }
          }
      }
  
      return success;
  }
  </code></pre>