java - SQL 返回计数字段的总和。不允许我删除 GroupBy

Question

我有一点复杂的(对我来说)SQL 查询。
我需要计算每月 14:00:00 到 16:00:00 之间的所有记录，并且必须从字段内的时间戳中提取小时才能执行此操作。在应用到Java程序之前我一直在用PGAdmin3 Postgre练习SQL语句

   SELECT
    extract(hour from trips.tpep_pickup_datetime) AS hour,
    count(*)  AS aCount
FROM
    trips

where 

extract(hour from trips.tpep_pickup_datetime) >=14 and 

extract(hour from trips.tpep_pickup_datetime) <=16 and

month ='Jan'

group by
    1,extract(month from trips.tpep_pickup_datetime); 

这给我带来了查询中的一月结果..我只想要总和

    hour  |  aCount
-----------------------
     16   |  16111 
     14   |  13301
     15   |  14796

!!!更新!!!是的:我曾尝试删除该组，但收到错误消息

    ERROR:  column "trips.tpep_pickup_datetime" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:     extract(hour from trips.tpep_pickup_datetime) as hour,
                              ^
********** Error **********

ERROR: column "trips.tpep_pickup_datetime" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 31

我实际上需要返回的总和，而不是分割。 (44,208)原因是我使用Java方法返回按月列出的给定时间之间的记录数。

[第 mnth 是月份名称的数组]

private void conRecTime(int time1, int time2) {
    String mnth;
    try {
        Statement stat1 = conn.createStatement();
        ResultSet rs1;
        for (int i = 0; i < monthsArr.length; i++) {
            mnth = monthsArr[i];

            rs1 = stat1.executeQuery(
                    "SELECT "
                    + "extract(hour FROM trips.tpep_pickup_datetime)AS hour,"
                    + "count(*) AS COUNT "
                    + "FROM trips "
                    + "WHERE "
                    + "extract(hour from trips.tpep_pickup_datetime) >="+ time1 
                    + "AND extract(hour from trips.tpep_pickup_datetime) <="+ time2
                    + "AND month ='"+ mnth + "'"
                    + "GROUP BY 1,extract(month from trips.tpep_pickup_datetime);");
            rs1.next();
            count = rs1.getInt(2);

            System.out.println("Count of records of " + mnth + " between the times " + time1 + " and " + time2 +  " is " + count + " records"  );
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

它只是读取每个月的第一行，即 16 小时(4 点)，但我无法在 print 语句中使用该方法读取 14 或 15 小时。

我有点卡住了，因为我似乎对 SQL 语句执行的任何操作都会把它弄乱并且只是不喜欢它(我尝试了一些嵌套语句，但它只是时好时坏)。这是我第一次使用 SQL 和 Java，因此我们将不胜感激有关如何构建 Java 方法或 SQL 语句的建议

Answer 1

如果您不需要分割，只需放弃 group by 子句，并从 SELECT 列表中删除除计数之外的所有内容:

SELECT COUNT(*)  AS aCount
FROM   trips
WHERE  EXTRACT(HOUR FROM trips.tpep_pickup_datetime) >= 14 AND
       EXTRACT(HOUR FROM trips.tpep_pickup_datetime) <= 16 AND
       month = 'Jan'