(Solved) Show the sum of a group
SELECT "Input"."Date", "Employee"."First Name",
SUM( "Show"."Hours" )
FROM "Input", "Show", "Employee" WHERE "Input"."ShowID" = "Show"."ID" AND "Input"."EmployeeID" = "Employee"."EmployeeID" AND "Input"."Date" = {d '2014-12-31' }
GROUP BY "Employee"."First Name"
But when I run the query I get the following error
*The Data content could not be loaded.
Not in aggregate function or group by clause: org.hsqldb.Expression@15321f0 in statement [SELECT “Input”.“Date”, “Employee”.“First Name”, SUM FROM “Input”, “Show”, “Employee” WHERE “Input”.“ShowID” = “Show”.“ID” AND “Input”.“EmployeeID” = “Employee”.“EmployeeID” AND “Input”.“Date” = ’2014-12-31’ GROUP BY “Employee”.“First Name”]*
What am I doing wrong?
Thanks
SELECT SUM AS “Amount”, “date”, “Account”, “TypeID”, “Comment”, “TypeID” FROM “TTransaction” GROUP BY “TTransaction”.“TypeID”
Looking at the example SQL on-line, this should be a trivial problem to solve.
but gives me the error;
Not in aggregate function or group by clause: org.hsqldb.Expression@99569a7 in statement [SELECT SUM AS “Amount”, “date”, “Account”, “TypeID”, “Comment”, “TypeID” FROM “TTransaction” GROUP BY “TTransaction”.“TypeID”]
regards,
Carlton.
SELECT SUM("Value") AS "Amount" FROM "TTransaction"
If you now want to have more then one result row, e.g. the Amount of “Value” grouped by “TypeID”, you need to define a group by condition
SELECT SUM("Value") AS "Amount", "TypeID" FROM "TTransaction" GROUP BY "TypeID"
You can have more then one group by condition e.g.
SELECT SUM("Value") AS "Amount", "Account", "TypeID" FROM "TTransaction" GROUP BY ""Account", "TypeID"
Trying to displaying a column, which is different in every row of the source table, e.g. “comment”, will result in no summing but displaying the original rows:
SELECT SUM ("Value") AS "Amount", "date", "Account", "TypeID", "Comment" FROM "TTransaction" GROUP BY "date", "Account", "TypeID", "Comment"
All displayed columns have to be in the group by condition, except the aggregated, here it’s
SUM("Value")
R
OR THIS