Postgresql get matched line of joined table

Question

I have a table where we have partners and orders are set.

|partner_id|order_id|
| -------- | ------ |
|1         |5       |
|1         |6       |
|2         |7       |
|4         |8       |
|4         |9       |
|4         |10      |

Here we have a table where partner information is set. Here id is partner_id from previous table.

|id|email               |
|--| ------------------ |
|1 |[email protected]|
|2 |[email protected]|
|4 |[email protected]     |

My question is there a any possibility by joining these tables get something like:

|partner_id|email               |sale_count|
| -------- | ------------------ | -------- |
|1         |[email protected]| 3        |
|4         |[email protected]     | 3        |

Basically my goal is to match orders by partner email and extract one of the partners with the same emails id.

Answer 1

SELECT P.ID,COUNT(O.ORDER_ID)AS CNTT,P.EMAIL
FROM ORDERS AS O
JOIN PARTNER_INFORMATION AS P ON O.PARTNER_ID=P.ID
GROUP BY P.ID,P.EMAIL

ORDERS-a table where we have partners and orders are set. PARTNER_INFORMATION -Here we have a table where partner information is set.

Answer 2

If you want a distinct email, then you should try;

SELECT
    email,
    SUM(sale_count) AS total_sale_count
FROM (
    SELECT
        pe.email,
        COUNT(po.order_id) AS sale_count
    FROM
        partner_order AS po
    JOIN
        partner_email AS pe
    ON
        po.partner_id = pe.id
    GROUP BY
        pe.email
) AS subquery
GROUP BY
    email;

otherwise you can't group the different ids from the same email.