HI guys
I have a problem in my database with retrieving the average quantity sold for the product and the average quantity sold for the category it's in. I have table c.categories with category_id from 1-6 with descriptions for example RTV, computers, phones, etc. pro.products where I have product_id with categories 1-6, and product description. ps product seller where are product_id with prices. and op. ordered products, where all products with quantities.
The main problem is that where I use AVG(c.category_id) is just showing me a number of categories, not the average quantity sold for the category.
Here is my code
SELECT c.category_description, pro.product_code, pro.product_description,
PRINTF("%.2f",AVG(op.quantity)) AS [Average sold quantity],
AVG(c.category_id) AS [Average quantity sold for category]
FROM categories c
LEFT OUTER JOIN products pro ON c.category_id = pro.category_id
LEFT OUTER JOIN product_sellers ps ON pro.product_id= ps.product_id
LEFT OUTER JOIN ordered_products op ON ps.product_id=op.product_id AND
ps.seller_id = op.seller_id
GROUP BY pro.product_id
ORDER BY c.category_description,pro.product_description
And a sample of the data :
https://www.filemail.com/d/txvyzhgoomztxzz
I am sorry for the structure , I'm just starting with SQLlite
Many thanks guys!