访问SQL查询 - 总计和小计 [英] Access SQL Query - Total and Sub-Total

问题描述

您好，

我在Access数据库中编写了以下传递查询，最初请求者只是想确定支付的总项目数和金额（70支付）总共700,000美元），但现在，他已经问我是否可以提供
&支付类型（支票或电子支付）（35支350,000美元，35支ACH，350,000美元）。我无法弄清楚如何实现这一点，是否可以添加第二组，如在小计中？我试过但到目前为止还没有成功。

SELECT   trunc（DISBURSEMENT_CHECK_DETAILS.D_CREATED_TIMESTAMP）Report_Date，

         count（DISBURSEMENT_CHECK_DETAILS.I_PAYMENT_REFERENCE_NUMBER）计数，

               DISBURSEMENT_CHECK_DETAILS.V_PAYMENT_TYPE_MCD，

         sum（DISBURSEMENT_CHECK_DETAILS.N_AMOUNT）金额，

         '支出'为"Report_Type"

  FROM   AONDBA.DISBURSEMENT_CHECK_DETAILS

  WHERE（DISBURSEMENT_CHECK_DETAILS.D_CREATED_TIMESTAMP> trunc（解码（to_char（sysdate，'dy'），'mon'，sysdate-3，sysdate-1）））

  group by trunc（DISBURSEMENT_CHECK_DETAILS.D_CREATED_TIMESTAMP）

---

Lorac1969

解决方案

你能发布原始查询吗？

因为第二级意味着你只需要在分组中添加第二列。

此SQL语法也是PL / SQL，因此是Oracle或Postgres。那个错误的论坛。

但是这应该这样做：

 SELECT trunc（D.D_CREATED_TIMESTAMP）Report_Date ，
 COUNT（D.I_PAYMENT_REFERENCE_NUMBER）计数，
 D.V_PAYMENT_TYPE_MCD，
 SUM（D.N_AMOUNT）金额，
'支付'AS" Report_Type" 
来自AONDBA.DISBURSEMENT_CHECK_DETAILS D 
 WHERE（D.D_CREATED_TIMESTAMP> trunc（解码（to_char（sysdate，'dy'），'mon'，sysdate  -  3，sysdate  -  1）））
 GROUP BY trunc（D.D_CREATED_TIMESTAMP），
 D.V_PAYMENT_TYPE_MCD; 

Hello,

I wrote the below pass through query in an Access database, originally the requestor just wanted to identify the total item count and dollar amount for disbursements (70 disbursements totaling $700,000), now however, he has asked if I could provide the count & amount by disbursement type (check or electronic) (35 checks for $350,000, 35 ACHs for $350,000). I can't figure out how to accomplish this, is it possible to add a second group by, as in a sub-total? I tried but haven't succeeded so far.

SELECT   trunc(DISBURSEMENT_CHECK_DETAILS.D_CREATED_TIMESTAMP) Report_Date,
         count(DISBURSEMENT_CHECK_DETAILS.I_PAYMENT_REFERENCE_NUMBER) Count,
               DISBURSEMENT_CHECK_DETAILS.V_PAYMENT_TYPE_MCD,
         sum(DISBURSEMENT_CHECK_DETAILS.N_AMOUNT) Amount,
         'Disbursements' As "Report_Type"
  FROM   AONDBA.DISBURSEMENT_CHECK_DETAILS
 WHERE (DISBURSEMENT_CHECK_DETAILS.D_CREATED_TIMESTAMP > trunc(decode(to_char(sysdate,'dy'),'mon',sysdate-3,sysdate-1)))
 group by trunc (DISBURSEMENT_CHECK_DETAILS.D_CREATED_TIMESTAMP)

---

Lorac1969

解决方案

Can you post your original query?

Cause a second level means that you just need to add that second column in your grouping.

Also this SQL syntax is PL/SQL, thus Oracle or Postgres. So wrong forum for that.

But this should do it:

SELECT  trunc(D.D_CREATED_TIMESTAMP) Report_Date ,
        COUNT(D.I_PAYMENT_REFERENCE_NUMBER) Count ,
        D.V_PAYMENT_TYPE_MCD ,
        SUM(D.N_AMOUNT) Amount ,
        'Disbursements' AS "Report_Type"
FROM    AONDBA.DISBURSEMENT_CHECK_DETAILS D
WHERE   ( D.D_CREATED_TIMESTAMP > trunc(decode(to_char(sysdate, 'dy'), 'mon', sysdate - 3, sysdate - 1)) )
GROUP BY trunc(D.D_CREATED_TIMESTAMP) ,
        D.V_PAYMENT_TYPE_MCD;

这篇关于访问SQL查询 - 总计和小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！