Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

sql sum over 3 tables

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
LIBRALEX
Asked:
LIBRALEX
  • 5
  • 2
  • 2
  • +1
1 Solution
 
pcelbaCommented:
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
 
jasonduanCommented:
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
 
pcelbaCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Lawrence BarnesCommented:
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
 
pcelbaCommented:
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
 
Lawrence BarnesCommented:
In his initial request I do not recall a date grouping, just a splitting of the total.  We'll see.
0
 
pcelbaCommented:
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
 
LIBRALEXAuthor Commented:
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
 
pcelbaCommented:
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
 
LIBRALEXAuthor Commented:
Thanks pcelba,

Cannot believe that one slipped under the radar.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now