Visual FoxPro 查询添加总计

Question

我有以下简单查询，它只是对 1 行或多行进行求和。基于独特的参考。

我想要做的是将总计列添加到按 JobRef 分组的 jobcharge.nAccrInv 的结果中，

即

Job_Ref / Name / Sales / Total

123 / VAT / 10.0

123 / DUTY / 10.0

123 / GHC / 10.0 / 30.0

SELECT DISTINCT ;

Job.cJobRef AS JobRef,;
Job.cName AS Customer_Name, ;
Job.cJobType AS JobType, ;
Job.cJobMode AS JobMode, ;
Job.cOrigin AS Org,;
Job.cDestination AS Dest,;
Job.cOwner AS Owner,;
jobcharge.cInvoiceDescr as [Invoice_Desc], ;
jobcharge.nAccrInv as [Accrued_Costs], ;
jobcharge.nCostInv as [Actual_Costs], ;
jobcharge.nSaleInv as [Sales], ;
( SELECT SUM(jobcharge.nAccrInv) AS SalesTotals FROM jobcharge WHERE NJOBID =3524); FROM job;INNER JOIN jobcharge ON job.nJob_Id = jobcharge.nJobId; WHERE job.cJobRef= "RSJC00001" AND job.cOwner = 'DBQ'

谢谢罗斯

Answer 1

（太多了，无法放入评论字段）...稍后会回答。

您的 nJobID = 3524 的目的是什么，您只对一份工作感兴趣吗？如果是这样，您的查询将仅提供作业 3525 的总数，无论从 JOB 的 WHERE 子句返回的所有作业如何。

我将处理查询，但您不需要 DISTINCT。

此外，您只有 jobCharge.nArrcInv、.nCostInv、.nSaleInv，但后来您使用 SUM( jobcharge.nAccrInv )...您的意图是获取应计成本总和、实际成本总和、每个工作的销售额总和描述？？？包括其他作业标题内容吗？

看起来您想要每种工作活动类型的各个子组总数以及整个工作中所有销售额的总计...也许您想要的是这个...

SELECT 
      Job.cJobRef AS JobRef,;
      Job.cName AS Customer_Name, ;
      Job.cJobType AS JobType, ;
      Job.cJobMode AS JobMode, ;
      Job.cOrigin AS Org,;
      Job.cDestination AS Dest,;
      Job.cOwner AS Owner,;
      JCSubTotals.Invoice_Desc,;
      JCSubTotals.Accrued_PerDesc,;
      JCSubTotals.Actual_PerDesc,;
      JCSubTotals.Sales_PerDesc,;
      JCFinalTotals.Total_Accrued,;
      JCFinalTotals.Total_Actual,;
      JCFinalTotals.Total_Sales;
   from ;
      ( select jc.nJobID,;
               jc.cInvoiceDescr as Invoice_Desc, ;
               sum( jc.nAccrInv ) as Accrued_PerDesc, ;
               sum( jc.nCostInv ) as Actual_PerDesc, ;
               sum( jc.nSaleInv ) as Sales_PerDesc ;
            from ;
               JobCharge jc;
            where ;
               jc.nJobID = 3524 ;
            group by ;
               jc.nJobID,;
               jc.cInvoiceDescr ) JCSubtotals ;
      JOIN ;
         ( select jc.nJobID,;
                  sum( jc.nAccrInv ) as Total_Accrued, ;
                  sum( jc.nCostInv ) as Total_Actual, ;
                  sum( jc.nSaleInv ) as Total_Sales ;
               from ;
                  JobCharge jc;
               where ;
                  jc.nJobID = 3524 ;
               group by ;
                  jc.nJobID ) JCFinalTotals ;
            ON JCSubtotals.nJobID = JCFinalTotals.nJobID ;
      JOIN Job ;
         on JCSubtotals.nJobID = Job.nID;

实际上我已经给了您更多的列在每一行上显示每个职位描述的总计，以及与整个职位相比的总计（应计、实际和销售额）。您始终可以忽略您不关心的列，但这为您提供了我认为您想要的内容的方法。

此外，我假设最后一个连接到“JOB”表（因为未提供），其主键列只是“nID”而不是“nJob_ID”（作为 jobCharge 表的外键）。

如果您希望这些结果涵盖整个作业集，我实际上只需从 JobCharge 查询中删除相应的“WHERE”子句即可。