PostgreSQL – Columns must appear in Group BY clauses or for polymeric functions

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 <> 0
ORDER BY table_a.creation_date_time DESC

static int count(const QString &table, const QString &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() << query.lastError();
 qWarning() << statement;
 }
 else
 {
 if (query.isActive() && query.isSelect() && query.first())
 {
 bool ok = false ;
 success = query.value(0).toInt(&ok);
 if (ok == false)
 {
 success = -1;
 return success ;
 }
 }
 }
 }

 return success;
}

I am updating the Qt software to make it compatible with SQLite and PostgreSQL.
I have a C method for calculating the elements of a given table of a given sub-sentence.

In SQLite, the following Worked and gave me a number N (count).

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 <> 0
ORDER BY table_a.creation_date_time DESC

Using PostgreSQL (I use 9.3), I have the following error:

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

If I add GROUP BY table_a.creation_date_time, it will give me a table with N rows.
I have read a lot about how DBMS allows you to omit the GROUP BY clause The content of the column in. Now, I’m just confused.

For those who are curious, the C method is:

static int count(const QString &table, const QString &clauses = QString(""))
{
 int success = -1;

 if (!table.isEmpty())
 { 
 QString statement = QString("SELECT COUNT(*) FROM ");
 statement.append(table);
 if (!clauses.isEmpty())
 {
 statement.append(" ").append(clauses) ;< br /> }
 QSqlQuery query;
 if(!query.exec(statement))
 {
 qWarning() << query.lastError();
 qWarning () << statement;
 }
 else
 {
 if (query.isActive() && query.isSelect() && query.first())
 { 
 bool ok = false;
 success = query.value(0).toInt(&ok);
 if (ok == false)
 {
 success =- 1;
 return success;
 }
 }
 }
 }

 return success;
}

< /p>

If you just count (*) on the table to get a single scalar value result, then must it be out of date in the current order?

Solution

Remove outdated sequences to obtain “standard” query behavior across multiple dbms