SQLite Forum

AVG funcion problem
Login

AVG funcion problem

(1) By anonymous on 2021-08-14 07:50:37 [link] [source]

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!

(2) By Keith Medcalf (kmedcalf) on 2021-08-14 08:44:37 in reply to 1 [link] [source]

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 pro.product group.

(3) By Keith Medcalf (kmedcalf) on 2021-08-14 08:58:07 in reply to 1 [link] [source]

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.

(4) By anonymous on 2021-08-14 09:06:15 in reply to 3 [link] [source]

average quantity of the product

(9) By Keith Medcalf (kmedcalf) on 2021-08-14 18:33:16 in reply to 4 [link] [source]

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.

(5) By John Dennis (jdennis) on 2021-08-14 12:11:50 in reply to 1 [source]

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.

(6) By anonymous on 2021-08-14 13:00:13 in reply to 5 [link] [source]

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"

(7) By anonymous on 2021-08-14 13:36:46 in reply to 5 [link] [source]

Maybe it will need a subquery , but I have no idea how to put it together

(8) By Keith Medcalf (kmedcalf) on 2021-08-14 18:29:36 in reply to 7 [link] [source]

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?

(10) By Kevin Youren (KevinYouren) on 2021-08-14 21:00:06 in reply to 1 [link] [source]

For this query, more information is required.

I downloaded your sample data, and had to add some ); characters to tidy up the sql.

Also, a table definition is missing:

Error: no such table: shopper_orders.