Solved

Microsoft SQL Queries

Posted on 2016-11-02
11
63 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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 6

Accepted Solution

by:
Mike in IT earned 250 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 48

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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 48

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 6

Assisted Solution

by:Mike in IT
Mike in IT earned 250 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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 48

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 28

Assisted Solution

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

Regards,
Pawan
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 59
export sql results to csv 6 35
SSIS with VPN COnnection 2 74
SQL Function NOT ROUND 9 9
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

770 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