CODEWITHSUNDEEP
mysqlsql
Juliano Lima
Juliano Lima

Reputation: 721

MySQL Count paid sales and orders

Here is the thing: I have this Payment_types, Sales, Payments and Orders tables, either a sale as an order generate a payment

My tables is thus:

Payment_types: id, name (may be a order or a sale)

Payments: id, payment_type_id, sale_id, order_id, price, paid, description

Sales: id, price

Orders: id, price

then, if the payment is a sale, the order_id is null and vice versa.

then how I will do select to display each table record sales or orders which have their price equal to the sum of the table payments related to that id? and would also like to know if my database is structured correctly.

Upvotes: 0

Views: 443

Answers (3)

ymog
ymog

Reputation: 161

You could try something like:

select Sales.id,
    Max(Sales.price) Sales_price,
    Sum(Payment.price) Payment_price
from Sales, Payment
where Sales.id = Payment.sale_id
group by Sales.id
having Max(Sales.price) = Sum(Payment.price)

Your query does not work as expected because you use a WHERE clause on the result of an aggregation, but you should use a HAVING-clause.

Upvotes: 1

Rakesh
Rakesh

Reputation: 77

Please try below once.

Select * 
from Payments p,sales s,ordes o
where (p.sale_id = s.id and p.price= s.price)
or (p.sale_id = o.id and p.price= o.price)
Order by p.id

Upvotes: 1

Oscar Pérez
Oscar Pérez

Reputation: 4397

You could try something like:

SELECT Sales.*
  FROM Sales
 WHERE Sales.price = (
                       SELECT SUM(Payments.price)
                         FROM Payments
                        WHERE Payments.sale_id = Sales.id
                     )

Upvotes: 1

Related Questions