none
Multiple Aggregate Functions On One Table

    Question

  • I have a table called Transactions with these columns : TransactionDate, DRCR,TransactionValue,AccountNumber

    TransactionDate - Date on which a transaction was done

    DRCR - Shows whether the transaction was a debit or credit

    TransactionValue - How much was transacted

    AccountNumber - The client's account number.

    My request is to do a Monthly High Low Balance Report.

    The report should show Highest Balance(maximum value of credit transactions),Lowest Balance(minimum value of debit transactions), TotalCredits(sum of all credit transactions), TotalDebits(sum of all debit transactions). These will be filtered using Transaction date. A user can select start date as 1 June 2013 and end date as 30 June 2013. So the report should show data filtered to this period.

    I tried this query but its filtering to the period specified.

    SELECT   AccountNumber,
                                 (SELECT        MAX(transactionvalue)
                                   FROM            Transactions
                                   WHERE        (trans_drcrindicatorname = 'Credit') AND (transactionaccount = @AccNumber) AND (transactiondatetime BETWEEN 
                                                             @StartDate AND @EndDate)) AS MAX_CREDIT,
                                 (SELECT        MIN(transactionvalue)
                                   FROM            Transactions
                                   WHERE        (transactiondrcr = 'Debit') AND (transactionaccount = @AccNumber) AND (transactiondatetime BETWEEN 
                                                             @StartDate AND @EndDate)) AS MIN_DEBIT,
                                 (SELECT        SUM(transactionvalue)
                                   FROM            Transactions
                                   WHERE        (transactiondrcr = 'Credit') AND (transactionaccount = @AccNumber) AND (transactiondatetime BETWEEN 
                                                             @StartDate AND @EndDate)) AS TOTAL_CREDITS,
                                 (SELECT        SUM(transactionvalue) AS Expr1
                                   FROM            Transactions
                                   WHERE        (transactiondrcr = 'Debit') AND (transactionaccount = @AccNumber) AND (transactiondatetime BETWEEN 
                                                             @StartDate AND @EndDate)) AS TOTAL_DEBITS
    FROM            Transactions 
                   


    Good is not good enough when best is expected !!!

    Saturday, July 20, 2013 5:10 PM

Answers

  • What if there are no transactions in an account?

    I hope joining the result with Account master details table.

    The below one gives query to get the required values,

    SELECT   AccountNumber,MAX(transactionvalue),SUM(transactionvalue)
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Credit'
    group by AccountNumber
    
    SELECT   AccountNumber,min(transactionvalue),SUM(transactionvalue)
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Credit'
    group by AccountNumber


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by chirochino Saturday, July 20, 2013 7:15 PM
    Saturday, July 20, 2013 5:21 PM

All replies

  • What if there are no transactions in an account?

    I hope joining the result with Account master details table.

    The below one gives query to get the required values,

    SELECT   AccountNumber,MAX(transactionvalue),SUM(transactionvalue)
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Credit'
    group by AccountNumber
    
    SELECT   AccountNumber,min(transactionvalue),SUM(transactionvalue)
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Credit'
    group by AccountNumber


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by chirochino Saturday, July 20, 2013 7:15 PM
    Saturday, July 20, 2013 5:21 PM
  • How do I join the queries as one result set ?

    Good is not good enough when best is expected !!!

    Saturday, July 20, 2013 5:39 PM
  • Try this,

    --DROP TABLE #TRANSACTION
    --CREATE TABLE #TRANSACTION (transactiondatetime DATETIME,trans_drcrindicatorname NVARCHAR(10),TransactionValue MONEY,transactionaccount NVARCHAR(10))
    --
    --INSERT INTO #TRANSACTION VALUES(GETDATE()-8,'Debit',19.19,'aaaaa')
    --INSERT INTO #TRANSACTION VALUES(GETDATE()-1,'Debit',100.19,'ccccc')
    --INSERT INTO #TRANSACTION VALUES(GETDATE()-4,'Credit',500.19,'aaaaa')
    --INSERT INTO #TRANSACTION VALUES(GETDATE()-4,'Credit',333.19,'aaaaa')
    --INSERT INTO #TRANSACTION VALUES(GETDATE()-2,'Credit',44.19,'aaaaa')
    --SELECT * FROM #TRANSACTION
    DECLARE @AccNumber NVARCHAR(10)
    SET @AccNumber= 'aaaaa'
    DECLARE @EndDate DATETIME,@StartDate DATETIME
    SET @StartDate=GETDATE()-10
    SET @EndDate=GETDATE()
    ;WITH CTEMIN AS (
    	SELECT transactionaccount,trans_drcrindicatorname,MIN(transactionvalue) LOWESTBAL FROM #TRANSACTION 
    	WHERE (trans_drcrindicatorname = 'Debit') AND (transactionaccount = @AccNumber) 
    	AND  (transactiondatetime BETWEEN @StartDate AND @EndDate)
    	GROUP BY transactionaccount,trans_drcrindicatorname
    ),CTEMAX AS (
    	SELECT transactionaccount,trans_drcrindicatorname,MAX(transactionvalue) HIGHESTBAL FROM #TRANSACTION 
    	WHERE (trans_drcrindicatorname = 'Credit') AND (transactionaccount = @AccNumber) 
    	AND  (transactiondatetime BETWEEN @StartDate AND @EndDate)
    	GROUP BY transactionaccount,trans_drcrindicatorname
    ),CTESUM AS (
    	SELECT transactionaccount,trans_drcrindicatorname,SUM(transactionvalue) TOTALTRANSACTION FROM #TRANSACTION 
    	WHERE (transactionaccount = @AccNumber) 
    	AND  (transactiondatetime BETWEEN @StartDate AND @EndDate)
    	GROUP BY transactionaccount,trans_drcrindicatorname
    ),CTEFINAL AS (
    	SELECT *,
    	(SELECT LOWESTBAL FROM CTEMIN B WHERE B.transactionaccount=A.transactionaccount AND B.trans_drcrindicatorname=A.trans_drcrindicatorname) 
    	AS LOWESTBAL,
    	(SELECT HIGHESTBAL FROM CTEMAX C WHERE C.transactionaccount=A.transactionaccount AND C.trans_drcrindicatorname=A.trans_drcrindicatorname) 
    	AS HIGHESTBAL
    	FROM CTESUM A 
    )
    SELECT * FROM CTEFINAL


    Regards, RSingh

    Saturday, July 20, 2013 5:51 PM
  • you can try this,

    select * from (
    SELECT   AccountNumber,MAX(transactionvalue),SUM(transactionvalue)
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Credit'
    group by AccountNumber
    ) C
    cross join 
    (
    SELECT   AccountNumber,min(transactionvalue),SUM(transactionvalue)
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Debit'
    group by AccountNumber) D
    ON C.AccountNumber=D.AccountNumber


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, July 20, 2013 5:53 PM
  • try this if the desired values are needed for all the accounts.

    select A.AccountNumber,C.max_credit,C.SUM_credit,D.min_debit,D.sum_debit from 
    Accountmaster A
    left join
    (
    SELECT   AccountNumber,MAX(transactionvalue) max_credit,SUM(transactionvalue)SUM_credit
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Credit'
    group by AccountNumber
    ) C
    ON A.AccountNumber=D.AccountNumber
    left join 
    (
    SELECT   AccountNumber,min(transactionvalue) min_debit,SUM(transactionvalue) sum_debit
    FROM            Transactions 
    where ransactiondatetime BETWEEN  @StartDate AND @EndDate
    and trans_drcrindicatorname = 'Debit'
    group by AccountNumber) D
    ON A.AccountNumber=D.AccountNumber


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, July 20, 2013 5:57 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Why did you post a narrative? And that vague narrative describes a bad design! We do not use Debit/Credit internally in computer accounting systems; that was from Friar Luca Pacioli in the 15th century! He did not have negative numbers, the concept of zero or matrices. Here is what a polite person who knows RDBMS might have posted instead:

    CREATE TABLE Transactions 
    (transaction_date DATE NOT NULL, 
     account_nbr CHAR(15) NOT NULL
     REFERENCES Accounts(account_nbr), 
     PRIMARY KEY (transaction_date, account_nbr), 
     transaction_amt DECIMAL (12, 2) NOT NULL);

    Your design is weird, because you can have only one transaction per day per account. There is no other way to have a key in this mess. Is there a transaction_nbr that you forgot? 

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Monthly_Report_Periods
    (monthly_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (monthly_report_name 
         LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00'),
     monthly_report_start_date DATE NOT NULL,
     monthly_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (monthly_report_start_date <= monthly_report_end_date),
    etc);

    Google for the month name format I am showing you; here is the query: 

    SELECT C.monthly_report_name,
           SUM(transaction_amt) AS monthly_balance,
           MAX (transaction_amt) AS transaction_credit_max,
           MIN(transaction_amt) AS transaction_debit_min,
           SUM(CASE SIGN(transaction_amt) 
               WHEN 1 THEN transaction_amt
               ELSE 0.00 END) AS transaction_credit_tot,
           SUM(CASE SIGN(transaction_amt) 
               WHEN -1 THEN transaction_amt
               ELSE 0.00 END) AS transaction_debit_tot
      FROM Calendar AS C, 
           Transactions AS T
     WHERE T.transaction_date BETWEEN monthly_report_start_date 
                              AND monthly_report_end_date 
     GROUP BY C.monthly_report_name;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 20, 2013 8:06 PM