SQL return a sum of a count field. Will not allow me to remove GroupBy

Question

I have a little bit of a complicated (to me) SQL query.
I need to count all the records between the hours of 14:00:00 and 16:00:00 per month and had to extract hour from the timestamp within the field to do so. I've been practicing the SQL statement with PGAdmin3 Postgre before applying to a Java program

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

This brings me Jan results within the query..I just want the sum

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

!!!!UPDATE!!! YES: I had tried to remove the group by, i get an error message

    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

I actually need the sum of that returned, rather then the breakdown. (44,208) The reason being im using Java method to return the number records between the given times listed by month.

[The mnth is an array of the month names]

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

It just reads the top line in for each month, which is hour 16 (4'oclock), but I can't reach hours 14 or 15 with that method within the print statement.

I'm a bit stuck, as anything I seem to do the SQL statement will mess it up and just doesn’t like it (I tried some nested statements, and it’s just been hit and miss). This is the first time I have worked with SQL and Java so advice on how to structure either the Java method or SQL statement would be appreciated

Answer 1

If you don't need the breakdown, just forgo the group by clause, and remove everything but the count from the SELECT list:

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'