Solved

Microsoft SQL Queries

Posted on 2016-11-02
11
48 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 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 4

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
 
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 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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 4

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 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 18

Assisted Solution

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

Regards,
Pawan
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Powershell SMO script not working. 18 102
SQL server 2008 SP4 29 35
DATETIMEOFFSET feature 1 5
Restrict result set 1 0
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now