CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
user731060
user731060

Reputation: 47

Aggregate functions and OrderBy

I have a query like the following one:

  SELECT case_filed_by,
         office_code,
         desg_code, 
         court_code,
         court_case_no,
         COUNT(office_code) as count 
    FROM registration_of_case 
   WHERE TRUE 
     AND SUBSTR(office_code, 1, 0) = SUBSTR('', 1, 0) 
ORDER BY court_code, court_case_no

I am getting the following error:

ERROR: column "registration_of_case.case_filed_by" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT case_filed_by,office_code,desg_code, court_code,court […]

Upvotes: 1

Views: 672

Answers (3)

Frank Bollack
Frank Bollack

Reputation: 25186

As you describe in your comments, you actually want the number of selected rows in a separate field of your result set.

You can achieve this by using a subselect for the count and the join these two queries.

Something like this:

  SELECT case_filed_by,
         office_code,
         desg_code, 
         court_code,
         court_case_no,
         office_code_count 
    FROM registration_of_case,
         (SELECT COUNT(office_code) AS office_code_count 
            FROM registration_of_case 
           WHERE TRUE 
             AND SUBSTR(office_code, 1, 0) = SUBSTR('', 1, 0)
         ) AS count_query
   WHERE TRUE 
     AND SUBSTR(office_code, 1, 0) = SUBSTR('', 1, 0) 
ORDER BY court_code, court_case_no

I couldn't test the query, but it should work or at least point you into the right direction.

Upvotes: 2

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324750

You are using COUNT(), which is an aggregate function, along with a number of fields that are not part of the GROUP BY (since there is none) or in the aggregate function (except office_code).

Now, in MySQL something like this is allowed because the engine will select one record from the group and return that (although the query cannot affect it in any way, that's usually okay). Postgresql clearly cannot. I don't use Postgresql and I can work it out.

If Postgresql has a "non-strict" mode, I suggest you enable that; otherwise, either correct your query or change database types.

I would suggest an appropriate query, if I knew what Postgresql does, and doesn't, allow.

Upvotes: 1

Madhana Kumar
Madhana Kumar

Reputation: 68

Add a group by clause like this,

"group by case_filed_by,office_code,desg_code,court_code,court_case_no"

Now try exceuting, it will work. The simple logic is if you want to use aggreagate function together with other columns in table, group by that columns. Check it out and comment if works

Upvotes: 0

Related Questions