none
Access SQL Query - Total and Sub-Total RRS feed

  • Question

  • 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

    Wednesday, March 22, 2017 3:35 PM

All replies

  • 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;

    Wednesday, March 22, 2017 3:44 PM