?
Solved

Microsoft SQL Queries

Posted on 2016-11-02
11
Medium Priority
?
88 Views
Last Modified: 2016-11-04
Hello, I am having trouble with writing some queries,
I want to find the branch name, account type, account number, transaction number and amount of transactions of accounts where the average transaction amount is greater than three times the (overall) average transaction amount of accounts of that type.

The table:
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 unsure how to start this query. Any help would be appreciated
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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41870912
can you provide some sample data for below -

amount of transactions of accounts where the average transaction amount is greater than three times the (overall) average transaction amount of accounts of that type.
1
 

Author Comment

by:Bob Tian
ID: 41870935
For example, if the average transaction amount of all business accounts is $2,000 then return transactions from business accounts where the average transaction amount for that account is greater than $6,000
0
 
LVL 8

Accepted Solution

by:
Mike in IT earned 1000 total points
ID: 41870967
Most of the tables that you show you don't need for the question that you asked. you will only need the Account, Branch, and Transaction tables. And if you don't care about the name of the Branch you don't even need the Branch table. Without sample data I can't give you specifics, but a god starting point would be this:

Select b.branchName [Branch Name],a.type [Account Type],a.accNumber [Account],T.transNumber [Transaction Number],count(T.transNumber) [# of Transactions]
From Account a
Join Branch b on a.branchNumber = b.branchNumber
Join Transactions T on T.accNumber = a.accNumber
where (sum(t.TransactionAmount)/count(T.transNumber)) > (3 * sum(TransactionAmount{all})/count(transNumber){all})
Group by a.type

Open in new window


This is just a bare bones attempt since I don't have concrete data to test on. Also since you are looking to compare based on transaction amount, you need to have that data available in the transaction table or you won't be able to compare on it.
1
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 49

Expert Comment

by:PortletPaul
ID: 41871084
Only in MySQL that I know of can you GROUP BY just one column, but have many more non-aggregating column in the select clause.

SELECT
      b.branchName         [Branch Name]
    , a.type               [Account Type]
    , a.accNumber          [Account]
    , T.transNumber        [Transaction Number]
    , COUNT(T.transNumber) [# of Transactions]
FROM Account a
JOIN Branch b ON a.branchNumber = b.branchNumber
JOIN Transactions T ON T.accNumber = a.accNumber
WHERE (SUM(t.TransactionAmount) / COUNT(T.transNumber)) > (3 * SUM(T.TransactionAmount) / COUNT(T.transNumber))
GROUP BY
      b.branchName
    , a.type
    , a.accNumber
    , T.transNumber

Open in new window

'm not quite sure what this means "and amount of transactions of accounts " i guess it is a count of transations and that is what Michael has used.

no points please
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41871335
I think the filter should be in the Having Clause, I think aggregates in where clause with group by will give the syntax error. Try..

--

SELECT
      b.branchName         [Branch Name]
    , a.type               [Account Type]
    , a.accNumber          [Account]
    , T.transNumber        [Transaction Number]
    , COUNT(T.transNumber) [# of Transactions]
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
    , T.transNumber
HAVING (  SUM(t.TransactionAmount) * 1. / COUNT(T.transNumber) ) > ( 3. * SUM(T.TransactionAmount) / COUNT(T.transNumber) )

--

Open in new window

2
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41871566
oops,
Yes, filtering on aggregated values must be in the having clause
can't believe I missed that
0
 
LVL 8

Assisted Solution

by:Mike in IT
Mike in IT earned 1000 total points
ID: 41871943
Yes, putting the filter in the 'having' clause would certainly be better than using a 'group by'. Though I think that using either one would work. I did say that my earlier response was just a starting point, the asker will have to do their own testing to find out what will work for them. It's difficult to give good advice when there is no data to test against.
1
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41871946
Agreed that sometimes when we just post queries without the data we may miss on the syntax.

:)

Regards,
Pawan
1
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41872063
Yes, putting the filter in the 'having' clause would certainly be better than using a 'group by'. Though I think that using either one would work.

To get the aggregations you need the group by.

To filter by aggregated values must be after the group by in the having clause.

They are rules of sql syntax, not options.
1
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41873442
Hi Bob Tian,
Any feedback on this?

Regards,
Pawan
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

764 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