Using subtracting two quantity of a table but not proper result

i have three table “Receipt”, “Issue”, “Total”.
“Reciept” table columns SlNo(Auto), ProductName, Quantity, Date
“Issue” table columns SlNo(Auto), ProductName, Quantity, Date
“Total” table columes SlNo(Auto), ProductName, RecieptTotal, IssueTotal, Balance
summing ProductName wise of Reciept table and updating "Total " RecieptTotal column
sSQL=update “Total” set “Total”.“IssueTotal” = SELECT SUM( “Issue”.“Quantity” ) FROM “Issue” where “Issue”.“ProductName”=“Total”.“ProductName” GROUP BY “Issue”.“ProductName”;
summing ProductName wise of Issue table and updating "Total " IssueTotal column
update “Total” set “Total”.“RecieptTotal” = SELECT SUM( “Reciept”.“Quantity”) FROM “Reciept”
WHERE “Total”.“ProductName” = “Reciept”.“ProductName”
GROUP BY “Reciept”.“ProductName”;
Till here its update Total table as column wise but when
sSQL=update “Total” set “Total”.“Balance” =SELECT SUM( “Reciept”.“Quantity” - “Issue”.“Quantity” ) FROM “Issue”, “Reciept” WHERE “Reciept”.“ProductName”= “Issue”.“ProductName” GROUP BY “Total”.“ProductName”;
Two ProductName-Jute(150), Paper(126) under “Reciept”.“Quantity” and Jute(82), Paper(67) under “Issue”.“Quantity”
but “Total”.“Balance” for Jute(128) and Paper(128) not shows proper result . i am stuck…pls help
)


Your question is not clear. Rewrite it using a spaced text with multiple lines.

i have three table “Receipt”, “Issue”, “Total”

“Reciept” table have columns SlNo(Auto), ProductName, Quantity, Date

“Issue” table have columns SlNo(Auto), ProductName, Quantity, Date

“Total” table have columes SlNo(Auto), ProductName, RecieptTotal, IssueTotal, Balance

Summing in “Issue” table using "Quantity " column and updating "Total " Table " IssueTotal " column
sSQL=update “Total” set “Total”.“IssueTotal” = SELECT SUM( “Issue”.“Quantity” ) FROM “Issue” where “Issue”.“ProductName”=“Total”.“ProductName” GROUP BY “Issue”.“ProductName”;
it works and update “Total”.“IssueTotal”

summing in" Reciept" table using "Quantity " column and updating “Total " RecieptTotal” column
sSQL=update “Total” set “Total”.“RecieptTotal” = SELECT SUM( “Reciept”.“Quantity”) FROM “Reciept” WHERE “Total”.“ProductName” = “Reciept”.“ProductName” GROUP BY “Reciept”.“ProductName”;
it works and update “Total”.“RecieptTotal”

now i using only “Total” table updated “Total”.“RecieptTotal” and “Total”.“IssueTotal” for “Balance”

sSql=SELECT "RecieptTotal" - "IssueTotal" AS "Balance" FROM "Total" 

This SELECT stament works but when UPDATE INTRODUCED then returns error … Single value expected

sSQL=update "Total" set "Total"."Balance" = SELECT "RecieptTotal" - "IssueTotal" FROM "Total"

i am stuck…pls help )

What is the database engine? Not embedded HSQLDB 1.8, I think.
You are very stingy with information …

Yes embedded HSQLDB

At least give people a chance to answer the question. You don’t respond very quickly to answers nor even bother to mark the answer was correct by clicking on the checkmark in the upper left of the answer most correct. There are a lot of questions and research to answer them. It takes time - sometimes days and then no one bothers to even say if your effort was worth it. PLEASE be considerate.

update "Total" set "Total"."Balance" =  "RecieptTotal" - "IssueTotal"

Duplicate here.

Thank for urs help and sorry for my act…

If the answer solves your question please tick the :heavy_check_mark:.

update “Total” set “Total”.“ProductName” = SELECT “Material”.“ProductName” FROM “Material” group by “Material”.“ProductName” gets error … Single value expected. can u help me out…

Since this question is new and different from the original, please ask as a new question so others may find it. A new question buried within a comment cannot be easily found by others. Also provide some info on “Material” table - which item are you trying to match? Thank You.