Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql sum over 3 tables

Posted on 2013-11-27
10
Medium Priority
?
514 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 43

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 43

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 43

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 43

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 43

Accepted Solution

by:
pcelba earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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