Marianne VAN WYK
asked on
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:
I would like to split the total by typename. Any ideas on how I may proceed from here?
Thanks!
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:
I would like to split the total by typename. Any ideas on how I may proceed from here?
Thanks!
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)
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
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
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
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
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.
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
We do have:
PaymentMethod : Payment ... 1:N
PaymentMethod : PaymentMethodType ... 1,0:N
which does not help
ASKER
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.
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.
I hope this helps to clarify what I want. Thanks again for your help!
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.
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.
I hope this helps to clarify what I want. Thanks again for your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks pcelba,
Cannot believe that one slipped under the radar.
Cannot believe that one slipped under the radar.
Open in new window