CODEWITHSUNDEEP
mysqljoin
lavirius
lavirius

Reputation: 477

Selecting from the same table twice with results on the same line

I want to generate a monthly report of total payments received from clients and total sent to suppliers in a single mySQL query that return these two totals on the same line. I can do it with UNION but that returns the results on different rows. All payments are in a payments table with the basic structure:

id | id_order | id_contact | amount | date_time

Update: contact_id can be either client or supplier. The related contacts table has a field contact_typewhich is either "SUP" or "CLI".

I would like the result to sum up all the payments received from clients and sent to suppliers during a time frame so I can generate the profit based on actual payments not the theoretical profit based on order sale price - my cost, especially because these orders are paid in installments over a long period of time so showing $5000 profit in June for an order placed in June is not relevant as long as the order is being paid 3000 in August and another 2000 in December. 

Month      | total $ sent to suppliers | total $ received from clients
June 2014    3000                        5000
July 2014    2500                        3800

Other supporting tables that I have are the "orders" table, the "contacts" table. Is there a way to do this with joins? Is this possible and OK performance wise and if not what other options do I have? (I am using MySQL 5.5 and will do this in PHP, if it matters)

Upvotes: 0

Views: 572

Answers (2)

lavirius
lavirius

Reputation: 477

This is the solution I came up with, based on @Gordon Linoff answer

select date_format(p.pay_dcreated, '%M %Y') as month,
sum(case when c.type_con = "FAC" then p.pay_amount else 0 end) as sent,
sum(case when c.type_con = "CST" then p.pay_amount else 0 end) as received
from payments_pay as p, contacts_con as c
where  p.pay_idcon=c.id_con
group by date_format(p.pay_dcreated, '%M %Y')
order by min(p.pay_dcreated);

This gives me a result I wanted.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You would use conditional aggregation. You don't provide key information, such as how you know whether someone is a supplier or client. The following is a sketch of what the query would look like:

select date_format(date_time, '%M %Y') as month,
       sum(case when contact_id = "supplier" then amount else 0 end) as sent,
       sum(case when contact_id = "client" then amount else 0 end) as received
from table t
group by date_format(date_time, '%M %Y')
order by min(date_time);

Note the order by. Because you are using a non-standard date format, this will still ensure that the rows are in temporal order.

Upvotes: 1

Related Questions