How to create 3 new columns out of Product_code,Product_name and avg price in sqlite

  • Thread starter Multimedica Sco
  • Start date
M

Multimedica Sco

Guest
I'm trying to create a new subquery and add the columns out of it to an already existing table. I want these columns to be set to values of 1- The product's code 2- The Product's name 3- The avg of the buying price of that product of course that needs a WHERE condition to set the avg of the price for the product to equal the entry that user gave.

I've already tried to create the subquery, add it to the table and take values out of it to assign it to the sell price as shown in the code below The main table's name is store2

c.execute('''UPDATE store2
INNER JOIN
(
SELECT Product_Name, AVG(Unit_Buying_Price)
FROM store2
GROUP BY Product_Name
) average2 ON store2.Product_Name = average2.Product_Name
SET store2.Unit_Buying_Price = average2.AVG(Unit_Buying_Price)
WHERE store2.Product_Code = ? ''',(Product_Codev.get()))


The new added columns should be in a table called average2 The Buying price is inserted via a textbox by the user The new columns should be created at the end of the table for each product code there should be the average of the buying price of that product. so it would seem like Product_Name| Avg_Price ----------------- Monitor|100$

and then that 100$ should be assigned to a new column that already exists called Unit_Sell_Price

When I try the code above it gives me this

Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\FUJITSU\AppData\Local\Programs\Python\Python37-32\lib\tkinter\_
_init__.py", line 1705, in __call__
return self.func(*args)
File "stores.py", line 155, in submit
WHERE store2.Product_Code = ? ''',(Product_Codev.get()))
sqlite3.OperationalError: near "INNER": syntax error

Continue reading...
 
Top