(Solved) Show the sum of a group

4 replies [Last post]
venoom27
Offline
Last seen: 3 weeks 3 days ago
Title: ★★★
Joined: 23 Nov 2014
Posts: 28
I am trying to do a query that involves three tables. I am limiting the results from the “Input” table with the date then grouping the results from the “Employee” table by first name and then I want a sum of hours from the “Show” table. I am trying to use the following code

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?

F3K Total
Offline
Last seen: 2 weeks 6 hours ago
Title: ★★★★★
Joined: 14 Oct 2013
Posts: 201
Show the sum of a group
TRY THIS

SELECT "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"

OR THIS


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 "Input"."Date","Employee"."First Name"
venoom27
Offline
Last seen: 3 weeks 3 days ago
Title: ★★★
Joined: 23 Nov 2014
Posts: 28
Yes that worked. Thanks
Yes that worked.

Thanks

carlton
Offline
Last seen: 1 year 4 weeks ago
Title: ★★
Joined: 5 Dec 2014
Posts: 14
I am having a similar problem
I am having a similar problem trying to get the SUM function to work correctly. Comparing the two SQL listings of the solution, they appear identical to that used in the original question. What was the original mistake? I am trying the following statement to obtain the sum of the amounts for each value of TypeID in the table;

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.

Carlton, Warminster UK
F3K Total
Offline
Last seen: 2 weeks 6 hours ago
Title: ★★★★★
Joined: 14 Oct 2013
Posts: 201
Show the sum of a group
Hi, if you want to sum up a single column let’s say “Value” the SQL would be

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

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.