sql - 聚合函数和 OrderBy

Question

我有一个类似下面的查询:

  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

我收到以下错误:

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 […]

Answer 1

正如您在评论中所述，您实际上希望在结果集中的单独字段中显示所选行的数量。

您可以通过对计数和连接这两个查询使用子选择来实现这一点。

像这样:

  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

我无法测试该查询，但它应该可以工作，或者至少可以为您指明正确的方向。