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); 

这给我带来了查询中的Jan结果。。我只想要的总和

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

实际上，我需要的是返回的总和，而不是明细。(44208)原因是我使用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点)，但我无法在打印语句中使用该方法达到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'