We help IT Professionals succeed at work.

sql sum over 3 tables

573 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!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

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

CERTIFIED EXPERT

Commented:
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

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
CERTIFIED EXPERT

Commented:
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.
In his initial request I do not recall a date grouping, just a splitting of the total.  We'll see.
CERTIFIED EXPERT

Commented:
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

Author

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!
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks pcelba,

Cannot believe that one slipped under the radar.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.