Solved

sql sum over 3 tables

Posted on 2013-11-27
10
443 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 41

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 41

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

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 41

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 41

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…

746 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