GROUP BY error - SQL

Question

This is the SQL query I have written. It works until right before the group by statement but once I add that part, I get this error:

'reading_datetime' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get

My query:

Select A.bill_account, hour(A.reading_datetime), A.reading_value
from (
    Select cast(cast(bill_account as double) as int)bill_account, reading_datetime, cast(reading_value as double)reading_value, `interval` 
    from amerendataorc
    WHERE cast(cast(`interval` as double)as int) = 3600 AND reading_datetime between '2015-03-15 00:00:00' and '2016-03-14 23:59:59'
) A       
GROUP BY A.bill_account
HAVING (COUNT(A.bill_account)>= 8000) and (COUNT(A.bill_account) < 9500)")

Not sure exactly how the group by is messing up the query.

Answer 1

take the sum of reading date time and reading value

Select A.bill_account, sum(hour(A.reading_datetime)), sum(A.reading_value)
from (
    Select cast(cast(bill_account as double) as int)bill_account, reading_datetime, cast(reading_value as double)reading_value, `interval` 
    from amerendataorc
    WHERE cast(cast(`interval` as double)as int) = 3600 AND reading_datetime between '2015-03-15 00:00:00' and '2016-03-14 23:59:59'
) A       
GROUP BY A.bill_account
HAVING (COUNT(A.bill_account)>= 8000) and (COUNT(A.bill_account) < 9500)")

---- explanation ------------

mysql> SELECT * FROM tt where user="user1";
+----------+-------+
| duration | user  |
+----------+-------+
| 00:06:00 | user1 |
| 00:02:00 | user1 |
+----------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tt where user="user1" group by user;
+----------+-------+
| duration | user  |
+----------+-------+
| 00:06:00 | user1 |
+----------+-------+
1 row in set (0.00 sec)

once you add group by it will give only the summery after group by on that column in above example its giving 1st value else you can get sum,max ... aggreagte values

Answer 2

SQL is trying to avoid an issue whereby you have multiple hour(A.reading_datetime) per A.Bill_Account. Grouping by Bill_account will give you a list of unique Bill_accounts. Then it has multiple hour(A.reading_datetime) per Bill_account and needs you to help it choose how to select one.

You need to group by each value that occurs or use aggregate functions on non-group by fields. If you group by reading_datetime and reading_value as well SQL will list all unique combinations of the three fields in the group by.

MySql suggests using first(); max() min() sum() etc are all aggregate functions what will help you get once value per Bill_account.

You will need to doing this for reading_value as well.

Answer 3

Standard SQL doesn't permit queries for which the select list refers to nonaggregated columns that are not named in the GROUP BY clause. Therefore you have to add those columns to the GROUP BY clause, or you have to aggregate the columns in the SELECT clause, in your case:

Select A.bill_account, sum(hour(A.reading_datetime)), sum(A.reading_value)

But you have to evaluate if it is adequate for your data to sum those columns in that way, and if it isn't, add the columns as GROUP BY criteria.

Answer 4

Any field that is not included in the Group By Clause will require an aggregate function like SUM, COUNT, MIN or MAX to be included in the Selected fields.

http://www.w3schools.com/sql/sql_groupby.asp

To correct the issue you will need to use the following group by clause

GROUP BY A.bill_account, A.reading_datetime, A.reading_value