Hello I have 2 tables:
There's a view in which I display a summary of the purchase and I would like to count how many items belong to certain purchase.
How do I achieve this in a single query?
I would like to create an extra column like total_items
, please note that Im using INNER JOINs to retrieve data from the clients and items tables, no problem with that.
count number of purchase_id
s in table purchase_items
and then join
So, query as follows (Using GROUP BY
):
SELECT
purchase_info.*,
T0.TOTAL_ITEMS
FROM purchase_info
LEFT JOIN (
select purchase_id,
COUNT(purchase_id) AS TOTAL_ITEMS
from purchase_items
GROUP BY purchase_id
) T0
ON purchase_info.id = T0.purchase_id
ORDER BY purchase_info.id DESC
Alternative query (using PARTITION BY
):
SELECT
purchase_info.*,
T0.TOTAL_ITEMS
FROM purchase_info
LEFT JOIN (
select DISTINCT
purchase_id,
COUNT(*) OVER (PARTITION BY purchase_id) AS TOTAL_ITEMS
from purchase_items
) T0
ON purchase_info.id = T0.purchase_id
ORDER BY purchase_info.id DESC
Don't use INNER JOIN
. If some purchase_info
data not available in purchase_items
then it will not capture in INNER JOIN
.
So, in this condition must use LEFT JOIN
.