Solved

Microsoft SQL query

Posted on 2016-11-02
7
55 Views
Last Modified: 2016-11-07
Hello, I am have trouble with this query,

Account number, balance, sum of transaction amounts, and balance - transaction sum for accounts in the New York branch that have at least ten transactions, order by account number.

Customer = {customerID, firstName, lastName, income, birthDate }
Account = {accNumber, type, balance, branchNumber(FK-Branch)}
Owns = {customerID(FK-Customer), accNumber(FK-Account)}
Transactions = {transNumber, accNumber(FK-Account), amount}
Employee = {sin, firstName, lastName, salary, branchNumber(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN(FK-Employee), budget}

I am confused on how to check if they have at least 10 transactions. I have done an inner  join Account, Branch and Transactions but am unsure how to deal with the number of transactions done per account.
0
Comment
Question by:Bob Tian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41871385
Do you have sample data and the expected output ?
1
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41871396
Try.. this

SELECT    
      a.accNumber          [Account]
	, a.balance            [balance]       
	, SUM(T.TransactionAmount) [Sum of Transactions]
	, a.balance - SUM(CASE WHEN branchNumber = 'New York' THEN T.TransactionAmount ELSE 0 END) Extracol
FROM Account a
INNER JOIN Branch b ON a.branchNumber = b.branchNumber
INNER JOIN Transactions T ON T.accNumber = a.accNumber
GROUP BY
      b.branchName
    , a.type
    , a.accNumber    
HAVING COUNT(T.transNumber) > 10
ORDER BY a.accNumber

Open in new window

1
 

Author Comment

by:Bob Tian
ID: 41873147
I think its b.branchName not b.branchNumber = 'New York'

Also the output would be something like:
AccNum  balance         Sum of Transactions     balance-Sum of Transactions
1               118231.13      12222                               106009.13
2               100808.03      2333                               98475.03

The problem is, I forgot to mention, is that the {transNumber} in Transaction does NOT indicate the amount of transactions done, it only represents a transaction number, combined with account number it uniquely identify a transaction. I am unsure how to find out how many transactions is done per account
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41873269
Yes it is branchName <<Typo>> :) Let me rewrite it for you ! Wait for sometime.
1
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41873346
Updated...

This is how we can count transactions for each account.
, COUNT(*) OVER (PARTITION BY a.accNumber) TransactionPerAccount

SELECT    
      a.accNumber          [Account]
	, a.balance            [balance]       
	, SUM(T.TransactionAmount) [Sum of Transactions]
	, a.balance - SUM(CASE WHEN branchName = 'New York' THEN T.TransactionAmount ELSE 0 END) Extracol
	, COUNT(*) OVER (PARTITION BY a.accNumber) TransactionPerAccount
FROM Account a
INNER JOIN Branch b ON a.branchNumber = b.branchNumber
INNER JOIN Transactions T ON T.accNumber = a.accNumber
GROUP BY
      b.branchName
    , a.type
    , a.accNumber    
HAVING COUNT(*) > 10
ORDER BY a.accNumber

Open in new window


You want only those records where each account has more than 10 transaction ? If Yes then use below -

SELECT * FROM 
(
	SELECT    
		  a.accNumber          [Account]
		, a.balance            [balance]       
		, SUM(T.TransactionAmount) [Sum of Transactions]
		, a.balance - SUM(CASE WHEN branchName = 'New York' THEN T.TransactionAmount ELSE 0 END) Extracol
		, COUNT(*) OVER (PARTITION BY a.accNumber) TransactionPerAccount
	FROM Account a
	INNER JOIN Branch b ON a.branchNumber = b.branchNumber
	INNER JOIN Transactions T ON T.accNumber = a.accNumber
	GROUP BY
		  b.branchName
		, a.type
		, a.accNumber  
)x
WHERE x.TransactionPerAccount > 10 	  
ORDER BY x.Account

--

Open in new window



Hope it helps !!
1
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41876682
Hi Bob,
Is this done?

Regards,
Pawan
0
 

Author Closing Comment

by:Bob Tian
ID: 41877539
thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question