Reporting Aggregated Data Using Group Functions| Banking Account Management System in SQL

6.Find the average transaction amount for each account.
SELECT AccountID, AVG(Amount) AS AverageTransactionAmount
FROM Transactions
GROUP BY AccountID;
Explanation
This SQL query picks out the AccountID and works out the average transaction amount for each account from the Transactions table. The AVG() function helps to calculate the average amount for each account. The query groups the results by AccountID to show the average transaction amount for each account in the output.

7.List the number of deposits made by each customer.
SELECT AccountID, COUNT(TransactionID) AS NumDeposits
FROM Transactions
WHERE TransactionType = 'Deposit'
GROUP BY AccountID;
Explanation
This query uses the account ID it then takes count of the transaction made using count(transactionID) and label it as NumDeposits from the table transaction. Where clause condition will filter the result to show only transactions having the label Deposit. After that entry obtained is joined with account ID for displaying number of deposits for each account ID. This query provides a summary of number of deposits for each account ID in Transactions table. Calculate the total withdrawals for each account type.

8.Retrieve the maximum transaction amount for each account.
SELECT AccountID, MAX(TransactionAmount) AS MaxTransactionAmount
FROM Transactions
GROUP BY AccountID;
Explanation
This query identifies the account ID and finds the maximum transaction amount (MaxTransactionAmount) for each unique account ID in the transaction table. Results are grouped by AccountID to show the maximum transaction amount for each account.

9.Find the minimum balance for each account type.
SELECT AccountType, MIN(Balance) AS MinBalance
FROM Accounts
GROUP BY AccountType;
Explanation
The query reads the AccountType id and calculates the minimum balance for every individual type of account in the accounts table.
The MIN(balance) function computes the minimum balance in each group of rows that share the same account type. The output displays the account type with minimum balance. Using the GROUP BY clause on AccountType. The query sorts the results by AccountType and guarantees that the minimum balance for individually unique account type is stated in the table.

10.Identify customers with an average account balance greater than 5000.
SELECT CustomerID
FROM Accounts
GROUP BY CustomerID
HAVING AVG(Balance) > 5000;
Explanation
The given query selects the CustomerID from the Accounts table and after that groups the data by that ID using the GROUP BY clause.
It then groups the data by customer ID using the GROUP BY clause.
Once the data is grouped the query filters the results using the HAVING clause.
The HAVING clause states a condition that must match for grouped or collected data.
In this scenario, the average balance for each customer (using Average (Balance)) must be greater than 5000.Only customer IDs that satisfy this condition are considered in the final result set.

11. Determine the total number of transactions for each account type.
SELECT accounttype, COUNT(transactionid) AS total_transactions
FROM transactions
GROUP BY accounttype;
Explanation
This query counts the number of transactions grouped by each single account type in the Transactions table. Each row in the result shows the account type and the total number of transactions associated with it.

12. Calculate the sum of transactions for each day.
SELECT tdate, SUM(amount) AS totalamount
FROM transactions
GROUP BY tdate
ORDER BY tdate;
Explanation
The given query selects the transaction date tdate and sum of the amount aliased as totalamount from the transactions table and groups them by the tdate column using GROUP BY clause. The query also includes ORDER BY Clause which sorts the the result in ascending order from oldest date to newest confirming that the output shows the total amount used on each date in sequential order.
In other words, it will only return transaction date (tdate) and sum of the amount for each transaction date. Or we can say that it will list sum of the amount date wise.

13.Find the average balance of customers with more than one account.
SELECT customer_id ,AVG(balance) AS average_balance
FROM accounts
GROUP BY customer_id
HAVING COUNT(*) > 1;
Explanation
The said SQL query that selects the customer_id and the average of the balance labelled as average_balance from the accounts table and groups them by the customer_id column. The query statement also includes a HAVING clause which fiters the groups of records based on contion. The condition mentioned here is that the count of all records for each group must be greater than 1. This means that the quey gives account wise average balance of customers who have more than one account.

14. Retrieve accounts with a balance greater than the average balance across all accounts.
SELECT account_id, balance
FROM accounts
WHERE balance > (SELECT AVG(balance) FROM accounts);
Explanation
The Query given above selects account_id and the balance from the accounts table based on WHERE clause which fiters the records based on a condition. The condition here is that the balance must be greater than an average balance determined by subquery, which selects the average balance from the accounts table.
The query shows only those accounts whose balance is greater than the average balance of all available accounts.

15. Calculate the total number of withdrawals for each account.
SELECT account_id,
COUNT(*) AS total_withdrawals
FROM transactions
WHERE transaction_type = 'withdrawal'
GROUP BY account_id;
Explanation
The said query provides a designed way to retrieve the number of withdrawal transaction related to every account. The query selects account_id and a count of the total numbers of withdrawal labelled as total_withdrawals. The query filters the result in the transactions table based on condition. The condition is that the transaction type should be withdrawal. The results are grouped for every accounts as per account_id. Which allow to review or summarize the total withdrawal for each account. This helps the banks to simply measure the account transaction activities related to withdrawal.

16. Identify accounts with a total transaction amount exceeding 10000.
SELECT accountid, SUM(amount) AS total_amount
FROM transactions
GROUP BY accountid
HAVING SUM(amount) > 10000;
Explanation
In the above given query the column accountid is the unique identifier for each account and amount is the individual amount of each transaction. The query selects the accountid and sum of transaction amount labelled as total_amount from the transactions table. The sum(amount) group function computes the total amount for each account. The query statement also includes HAVING clause which filters the groups based on the condition. The condition here is that the sum of all transaction amount should be more than the 10000. The query is used to identify significant transaction trends in across the accounts.

17. Find the maximum balance for customers with multiple accounts.
SELECT customerid, MAX(balance) AS max_balance
FROM accounts
GROUP BY customerid
HAVING COUNT(accountid) > 1;
Explanation
In the given above query we have selected customerid with Max(balance) from the account table. The result or output of the query are grouped for each customer as per customerid using the GROUP BY clause, which makes the groups according customerid.
The results are grouped for every customer according to customerid. Which permit us to review or summarize the maximum balance of accounts for each customer.
The HAVING clause filters condition filter out the customers who do not have more than one account by using the condition HAVING (COUNT(accountid)>1.
This query successfully eliminates customers having multiple accounts.

18. Retrieve the number of accounts with balances less than 1000.
SELECT AccountID, COUNT(*) AS NumberOfAccounts
FROM Accounts
WHERE balance < 1000 GROUP BY AccountID;
Explanation
The query selects the accountid and count (*) i.e the number of accounts from the accounts table. The WHERE clause filter condition filters the accounts which have balance less than 1000 using the the condition WHERE balance<1000.
The results are grouped for each account as per accountid using the GROUP BY clause, which makes the groups according accountid.
The final result results are grouped for each account according to accountid. Which permit us to summarize the total number of accounts which have balance less than 1000.
This query effectively retrives the total number of accounts with balance less than 1000.

19. Find the customers who have performed more than 5 transactions.
SELECT CustomerID, COUNT(*) AS TransactionCount
FROM Transactions
GROUP BY CustomerID
HAVING COUNT(*) > 5;
Explanation
The query retrives the customerid and count (*) count the total number of transaction labelled as TransactionCount from the transactions table.
The results are grouped together for each customer as per customerid using the GROUP BY clause, which makes the groups according customerid.
The HAVING COUNT (*)>5 condtion filters the groups based on the condition. The condition here is that the count of transactions should be more than 5. The query is useful to find customers who have performed more than 5 transactions.

20. Calculate the total and average amount transaction amount for deposits and withdrawals.
SELECT TransactionType,SUM(Amount) AS TotalAmount,
AVG(Amount) AS AverageAmount
FROM Transactions
WHERE
TransactionType IN ('Deposit', 'Withdrawal')
GROUP BY
TransactionType;
Explanation
The column TransactionType is the special unique identifier for each transaction type. The query selects the Transaction type and sum of transaction amount labelled as TotalAmount, average of amount labelled as AverageAmount from the transactions table only for those transaction type is ‘Deposit’ or ‘Withdrawal or both.
The WHERE clause filter condition, only includes the transaction type ‘Deposit’ or ‘withdrawal’.
The results are grouped for each transaction type by using the GROUP BY clause, which makes the groups according Transaction Type.
The query helps us to review transaction type along with total and average amount for Deposit and withdrawal.

21. Write query to find the total number of transactions per day having more than 5 transactions.
SELECT tDate, COUNT(*) AS TotalTransactions
FROM Transactions
GROUP BY tDate
HAVING COUNT(*) > 5;
Explanation
The query couts the total numbers of transactions performed tdate wise and only list only those transactions which has more than 5 transactions on the tdate.

22. What is query to list all customer’s id who have made deposits totaling more than 2000.
SELECT CustomerID, SUM(Amount) AS TotalDeposits
FROM Transactions
WHERE TransactionType = 'Deposit'
GROUP BY CustomerID
HAVING SUM(Amount) > 2000;
Explanation
The query retrives all list of the customerid from the transactions table along with total amount deposits more than 2000.
WHERE TransactionType = 'Deposit' precisely filters the transactions where the TransactionType is 'Deposit'.
The HAVING SUM (Amount)>2000 condtion filters the groups based on the condition. The condition here is that the sum of amount should be more than 2000. The query is useful to find customers who have performed total Deposite more than than 2000.