AVG funcion problem
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 :
I am sorry for the structure , I'm just starting with SQLlite
Many thanks guys!
avg(category_id) will average, strangely, the
category_id for the GROUP BY.
avg(sold) will average, strangely enough, the
sold for the GROUP BY.
Note, however, that you
GROUP BY pro.product so the above averages are calculated for each
Also, what is
average quantity sold?
What are the units? Containers per Fortnight? Shots per second (I imbibe expeditiously)?
Your SQL probably does not work because you cannot define the problem, so lets start there.
average quantity of the product
And that is, what? You have merely restated exactly the same thing with a slightly different wording but without shedding any light whatsoever on what it is you are trying to achieve.
No wonder you do not know how to solve the problem, you do not even know what it is and cannot even define what you want computed.
The necessary starting place is to understand the data you have and the computation you desire to make from it, then everything will fall into place.
Is the AVG(op.quantity) result correct. Your query does a GROUP BY product_id.
I think you need a very similar query with AVG(op.quantity) and a GROUP BY category_id, so that the average will be calcualted by category.
I understand, by if I GROUP BY , then the result for all tables is changing, how can I use the GROUP BY category_id only for 1 column which will be c.category_id. . because I want to to have op.quantity AS [Quantity of sold items and another column category_description AS [ Average quantity of sold items for all group ]. If I use GROUP BY category_id instead of product_id, then I don't have the rows for each product for example "Samsung TV, Playstation, Samsung S7", I have only categories like "Gaming", RTV", "Mobile phones"
Maybe it will need a subquery , but I have no idea how to put it together
Maybe you should define the problem first. If you cannot define exactly what result you want, then it is impossible to "put it together". You need to know that you want to paint the walls RED before you can go to the store and buy RED paint.
If you state that your goal is merely to "paint the room", then a reasonable question is "what colour".
Similarly you need to state the problem before solving it. If you do not know what the problem is, then how do you expect to be able to solve it?