Search code examples
sqlsql-serverderby

SQL COUNT FORM JOIN TABLES

I have the following sql command:

SELECT "USERNAME"."TOPICS".VALUE,
       "USERNAME"."TOPICS".QID,
       "USERNAME"."QUESTION".QRATING
FROM "USERNAME"."TOPICS" JOIN "USERNAME"."QUESTION"
ON "USERNAME"."TOPICS".QID = "USERNAME"."QUESTION".QID 
AND "USERNAME"."TOPICS".VALUE = 'kia'
ORDER BY QRATING DESC

It works really well, but I want to count how many element returns. So I tried to use:

SELECT COUNT("USERNAME"."TOPICS".QID)
FROM "USERNAME"."TOPICS" JOIN "USERNAME"."QUESTION"
ON "USERNAME"."TOPICS".QID = "USERNAME"."QUESTION".QID 
AND "USERNAME"."TOPICS".VALUE = 'kia'
ORDER BY QRATING DESC

But I get the error :

Column reference 'USERNAME.TOPICS.VALUE' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.

What is the problem?

Solution

  • Hmmm. The ORDER BY should be getting the error, not the SELECT. However, your query would be much easier to understand using table aliases:

    SELECT COUNT(t.QID)
FROM "USERNAME"."TOPICS" t JOIN
     "USERNAME"."QUESTION" q
     ON t.QID = q.QID AND t.VALUE = 'kia';

    If the first query works, I see no reason why this would not (and your original without the ORDER BY should also work).