Sum subquery (1 Viewer)

[Mina Garas Daniel]

Hello everyone
I have a simple question I have a table containing
Code , description , qty and sum columns
I need to make sum of qty in sum column
I try to use subquery instead of dsum to get better performance as I read on this forum but it does not work is any way to do that specially my database working via shared network folder

Thanks

 

[Pat Hartman]

context is everything. You can create a query that joins the two tables and that would be the most efficient way to calculate the sum. However, this query will be not updatable AND will not contain any detail. I don't know what the picture is showing and I can't read the headers. It looks like a report so in a report, you don't care if the recordset is updateable or not.

 

[Mina Garas Daniel]

context is everything. You can create a query that joins the two tables and that would be the most efficient way to calculate the sum. However, this query will be not updatable AND will not contain any detail. I don't know what the picture is showing and I can't read the headers. It looks like a report so in a report, you don't care if the recordset is updateable or not.

Thanks for your response
No its not for report this data in table and I need to calculate sum of qty to calculate percentage by divide qty / SumOfQty
That's all
And may I use SumOfQty in another calculation in another query
Thanks

 

[tvanstiphout]

@Pat: the screenshot is of Excel.

 

[CJ_London]

but it does not work is any way to do that specially my database working via shared network folder

Does not work means what exactly?

would help if you provide the sql to what you tried, may be just a simple correction

 

[ebs17]

SELECT
   T.Code,
   T.description,
   T.qty,
   Q.SumQ,
   T.qty / Q.SumQ AS Part
FROM
   TableX AS T
      INNER JOIN
         (
            SELECT
               Code,
               description,
               SUM(qty) AS SumQ
            FROM
               TableX
            GROUP BY
               Code,
               description
         ) AS Q
         ON T.Code = Q.Code
            AND
         T.description = Q.description

For better performance, subquery Q should be executed in the FROM part, as it only needs to be executed once here. In contrast, if you place the subquery in the SELECT part, you would have a correlating subquery, which means that the subquery has to be executed every time for each record in the TableX table, which is much more effort.

In addition, it would be recommended if there was a composite index over the Code and description fields; this helps significantly with grouping and linking.

may I use SumOfQty in another calculation in another query

If the (sub)query is used multiple times, it increasingly makes sense to save it as a separate query and use it that way.