Click here to Skip to main content
15,881,027 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have 4 tables in my database SQLITE as following:
1- products
2- customers
3- sales
4- purchases

sales table has product ID (pro_id) and customer ID (cu_id) I am trying to join customers and products in sales using inner join but my problem is I am trying to get values of AVG prices from another table purchases this is my queries:

SELECT sa_id, sa_date, p_name, cu_name, sa_quantity, sa_price, sa_amount FROM sales INNER JOIN products ON products.p_id = sales.pro_id INNER JOIN customers ON customers.cu_id = sales.cu_Id


SELECT Sum(pur_quantity * pur_price) / Sum(pur_quantity) As AVG_Price FROM purchases group by p_id


What I have tried:

I tried this below query:

SELECT sa_id, sa_date, p_name, cu_name, sa_quantity, sa_price, sa_amount (SELECT Sum(pur_quantity * pur_price) / Sum(pur_quantity) As AVG_Price FROM purchases group by pro_id) AVG_Price FROM sales INNER JOIN products ON products.p_id = sales.pro_id INNER JOIN customers ON customers.cu_id = sales.cu_id
Posted
Updated 21-May-23 10:40am
v3
Comments
[no name] 21-May-23 9:53am    
Use "intermediate" queries if you can't get your head around doing it in "one statement". It never gets easier.
PIEBALDconsult 21-May-23 17:05pm    
OUTER JOIN ?

The relations between tables are not quite clear but are you looking for a correlated subquery? Something like

SQL
SELECT sa_id, 
       sa_date, 
       p_name, 
       cu_name, 
       sa_quantity, 
       sa_price, 
       sa_amount,
       (SELECT Sum(pur.pur_quantity * pur.pur_price) 
               / Sum(pur.pur_quantity)
        FROM purchases pur
       WHERE pur.pro_id = sales.pro_id) AS AVG_Price 
FROM sales sales
INNER JOIN products  ON products.p_id = sales.pro_id 
INNER JOIN customers ON customers.cu_id = sales.cu_id
 
Share this answer
 
Comments
brahim farhat (AAD) 21-May-23 16:41pm    
There was a typo i updated my question see now?
Wendelius 22-May-23 0:14am    
I see that you removed the underscore but what about the actual question. Is the suggested subquery correlation what you're after?

WHERE pur.pro_id = sales.pro_id
brahim farhat (AAD) 23-May-23 21:16pm    
Here is my final solution i tried to post the answer but it says
 inmodorate
:

SELECT sa_id, 
       sa_date, 
       p_name, 
       cu_name, 
       sa_quantity, 
       sa_price, 
       sa_amount,
       Sum(pur_quantity * pur_price) 
               / Sum(pur_quantity)
        AS AVG_pur_Price 
FROM sales
JOIN products  ON products.p_id = sales.pro_id 
JOIN customers ON customers.cu_id = sales.cu_id
join purchases on purchases.pro_id=products.p_id
group by sa_id
I solved my problem by this query :

SELECT sa_id, 
       sa_date, 
       p_name, 
       cu_name, 
       sa_quantity, 
       sa_price, 
       sa_amount,
       Sum(pur_quantity * pur_price) 
               / Sum(pur_quantity)
        AS AVG_pur_Price 
FROM sales
JOIN products  ON products.p_id = sales.pro_id 
JOIN customers ON customers.cu_id = sales.cu_id
join purchases on purchases.pro_id=products.p_id
group by sa_id
 
Share this answer
 
v2
Comments
PIEBALDconsult 23-May-23 22:03pm    
Please don't try to answer your own question. Just use the Improve question button to add detail and such.
brahim farhat (AAD) 24-May-23 16:15pm    
Ok

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900