Solved

sql sum over 3 tables

Posted on 2013-11-27
10
504 Views
Last Modified: 2013-11-28
Hi,

I have the following query:
SELECT pm.Name AS Method, pmt.Name as TypeName,
         Sum(Amount) AS Total
FROM Payment p
inner JOIN PaymentMethod pm ON p.MethodID = pm.ID
left JOIN PaymentMethodType pmt ON pm.ID = pmt.PaymentMethodID
WHERE p.ID > 0
GROUP BY pm.Name, pmt.Name
ORDER BY pm.Name

It gives me the result below:
my sql query results
I would like to split the total by typename. Any ideas on how I may proceed from here?

Thanks!
0
Comment
Question by:LIBRALEX
[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
  • 2
  • +1
10 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39681009
Do you mean to remove the Method column from output?
SELECT pmt.Name as TypeName, 
         Sum(Amount) AS Total
FROM Payment p
left JOIN PaymentMethodType pmt ON pm.ID = pmt.PaymentMethodID
WHERE p.ID > 0
GROUP BY pmt.Name 
ORDER BY pmt.Name

Open in new window

0
 
LVL 11

Expert Comment

by:jasonduan
ID: 39681019
try this:

SELECT ISNULL(pmt.Name, pm.Name) AS TypeName,
         Sum(Amount) AS Total
FROM Payment p
inner JOIN PaymentMethod pm ON p.MethodID = pm.ID
left JOIN PaymentMethodType pmt ON pm.ID = pmt.PaymentMethodID
WHERE p.ID > 0
GROUP BY ISNULL(pmt.Name, pm.Name) 
ORDER BY ISNULL(pmt.Name, pm.Name)

Open in new window

0
 
LVL 42

Expert Comment

by:pcelba
ID: 39681055
Sorry, forget my first answer please. It cannot work...

To split the total by TypeName you would need the TypeName identification in each payment. Do you have such column in Payment table?

The existing query cannot recognize PaymentMethodType for each Payment because it is related to the PaymentMethod only.

Something like
SELECT pm.Name AS Method, pmt.Name as TypeName, 
         Sum(Amount) AS Total
FROM Payment p
inner JOIN PaymentMethod pm ON p.MethodID = pm.ID
left JOIN PaymentMethodType pmt ON p.PaymentMethodTypeID = pmt.ID
WHERE p.ID > 0
GROUP BY pm.Name, pmt.Name 
ORDER BY pm.Name

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39682156
It almost sound like you want to pivot the Type Name into a column.  This is how I would do that.
SELECT *
FROM (      SELECT pm.Name AS Method, ISNULL(pmt.Name,'No Type') as TypeName, Sum(Amount) AS Total
            FROM Payment p
            inner JOIN PaymentMethod pm ON p.MethodID = pm.ID
            left JOIN PaymentMethodType pmt ON pm.ID = pmt.PaymentMethodID
            WHERE p.ID > 0
            GROUP BY pm.Name, pmt.Name
            /*ORDER BY pm.Name*/ ) AS data
PIVOT (      SUM(Total) FOR TypeName IN([Visa],[Master Card],[FNB],[Standard Bank],[Absa],[No Type])) AS pvt

LVBarnes
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39682325
Yes, that's one possibility but we still have no info about correct table joining to do this task. The Payment Method Type must be stored in Payment table or in Payment Method table. Now we have Payment Method stored in Payment Method Type which does not allow requested data grouping.
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39682335
In his initial request I do not recall a date grouping, just a splitting of the total.  We'll see.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39682362
Yes, but we cannot split the Total based on TypeName because we still don't have the Payment - PaymentMethodType relation defined.

We do have:
PaymentMethod : Payment ...  1:N
PaymentMethod : PaymentMethodType  ...  1,0:N
which does not help
0
 

Author Comment

by:LIBRALEX
ID: 39682849
Hi,

Thank you for your answers. To answer some of your questions - I have 3 tables:
Payment : PaymentMethod ... N : 1
PaymentMethod : PaymentMethodType ... 1,0 : N
There is no direct link from Payment to PaymentMethodType.
payments relationships - Payment, PaymentMethod & PaymentMethodType
If you look at the results of my query below, what I want to do (line 4 & 5) is to
show the method 'Credit Card' and the method type 'Visa'/'Master Card' with their respective totals. Currently it gives me the total of Visa & Master Card together on both rows - not split up between the two method types.

the two rows should not have the same result
I hope this helps to clarify what I want. Thanks again for your help!
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39682954
Yes, I understand what you want and this everything is clear except one fact: How did you split 34513481 into two numbers? Is it based on SubMethod column in Payment table?

If not then you cannot identify Payment records belonging to each PaymentMethodType.

If yes then please confirm SubMethod contains PaymentMethodType.ID

In other words:
You have 10 Payments belonging to e.g. Credit Card
And you have 3 PaymentMethodType(s) belonging to Credit Card  (e.g. Visa, Master Card, AMEX)
How do you recognize which Payment belongs to each PaymentMethodType ?

The only possibility I see in your data model is the Payment.SubMethod. So what contains this column?
0
 

Author Comment

by:LIBRALEX
ID: 39682990
Thanks pcelba,

Cannot believe that one slipped under the radar.
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

626 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