SUMmarize 3 columns from 2 different table in SQL

Question

I have 3 database table. The one I store sell data like invoice number, due date, issue date. The second table I have the sell record id stored from previous table, there I store the ammount the person have payed (something like the customer payed only a half of the total price). In the third table I store the products and every row has the sell record id for backreference, in this table I have quantity and subtotal (quantity * price).

What I need is select all the record from first table and based on the sell_id I have to summarize how much they payed from the second table, summarize how many products one purchased and how much does it cost. I need a little guidance to the right solution because I am stuck. So far I came to this uggly sollution:

SELECT 
    (SELECT SUM(payment.sellpayment_amount) FROM es_sellpayment payment WHERE sell.sell_id = payment.sell_id) AS payed,
    (SELECT SUM(product.sellproduct_quantity) FROM es_sellproduct product WHERE sell.sell_id = product.sell_id) AS quantity,
    (SELECT SUM(product.sellproduct_total) FROM es_sellproduct product WHERE sell.sell_id = product.sell_id) AS total
FROM es_sell sell

It works I get the correct result, but I am unsure/unaware how this will impact later on the performance of the website. I tried the classic left join which gave me wrong results. I am also thinking storing permanently these 3 values on the main table; but this would be the lest preferable method because I had to make at lest 2 seperate SQL queries to retrieve the records.

CREATE TABLE `es_sell` (
`sell_id` int(11) NOT NULL,
`sell_invoice` int(11) NOT NULL,
`sell_note` text COLLATE utf8mb4_unicode_ci NOT NULL,
`sell_deliver` datetime NOT NULL,
`sell_issued` datetime NOT NULL,
`sell_due` datetime NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `es_sellproduct` (
`sellproduct_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`sellproduct_quantity` int(11) NOT NULL,
`sellproduct_price` int(11) NOT NULL,
`pricetype_id` int(11) NOT NULL,
`sellproduct_total` decimal(11,3) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `es_sellpayment` (
`sellpayment_id` int(11) NOT NULL,
`sellpayment_amount` decimal(11,3) NOT NULL,
`sell_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

You are using aggregates, read about them before you ask a question. Aggregates use `group by`. An aggregate without it implicitly groups over all columns, giving one group. Join your tables. Why would think of using left join? It returns inner join rows plus unmatched left table rows extended by nulls. — philipxy, Dec 17 '17 at 23:00
Hello. Yes I was playing around with something like this `SELECT SUM(pa.sellpayment_amount), SUM(pr.sellproduct_quantity), SUM(pr.sellproduct_total) FROM es_sell s LEFT JOIN es_sellpayment pa ON s.sell_id = pa.sell_id LEFT JOIN es_sellproduct pr ON s.sell_id = pr.sell_id GROUP BY s.sell_id` either way I got wrong result. With only one left join using sellpayment it does work without group by too. — zsoro, Dec 17 '17 at 23:03
Possible duplicate of [Multiple Self-Join based on GROUP BY results](https://stackoverflow.com/questions/47758492/multiple-self-join-based-on-group-by-resultshttps://stackoverflow.com/q/47857156/3404097). See my comments at the recent duplicate https://stackoverflow.com/q/47857156/3404097. — philipxy, Dec 17 '17 at 23:13

score 0 · Accepted Answer · answered Dec 17 '17 at 23:39

Use sub-queries to aggregate the associated tables to ensure that you're only ever joining one row to one row...

SELECT
    sell.sell_id,
    payment.payed,
    product.quantity,
    product.total
FROM
    es_sell          sell
LEFT JOIN
(
    SELECT
        sell_id,
        SUM(sellpayment_amount)   AS payed
    FROM
        es_sellpayment payment
    GROUP BY
        sell_id
)
    payment
        ON payment.sell_id = sell.sell_id
LEFT JOIN
(
    SELECT
        sell_id,
        SUM(sellproduct_quantity)   AS quantity,
        SUM(sellproduct_total   )   AS total
    FROM
        es_sellproduct
    GROUP BY
        sell_id
)
    product 
        ON product.sell_id = sell.sell_id

If sell_id isn't unique in the sell table, you can aggregate again in the outer query.

I admit I need to spend more time learning and not just jumping in the job boat and ultimately crash against a giant rock. You +MatBailie, you are a hero for me, you saved me! Thank you! This works flawlessly, amazing! I promise here that I am going to read and practice more. — zsoro, Dec 17 '17 at 23:54

SUMmarize 3 columns from 2 different table in SQL

1 Answers1