Python tKinter sQL. How to add a column of values?

[dewijones67]

Hello,

I'm trying to calculate the total of a column of values using an sQL DB.
The values are generated using a query.

I have an Orders table: within that the 'Quantity' column is multiplied by the 'Price' column to create a 'Totals' column. I am joining 2 tables to gain the values.
Therefore, when I use the SELECT SUM(Totals) command it tells me that there is no column called Totals.

My query is as follows:

1 2 3 4 5

c = myshop_db.execute('''SELECT Orders.Cust_Num, Orders.Order_Date, Orders.Prod_Num, Products.Prod_Name, Orders.Prod_Qty, Products.Prod_Cost, Orders.Prod_Qty * Products.Prod_Cost as Total     FROM Orders     INNER JOIN Products     ON Orders.Prod_Num = Products.Prod_Num     WHERE Orders.Order_Num = ?''', num)

Therefore, it is 'Total' I need to calculate.

Further to this, can I display this new value using the same 'Tree' 'Frame' that I am using to display this query?
I really hope this can be done, obviously I'll be very grateful for any suggestions or alternatives.

Many thanks in advance,
Dewi :)

[Larz60+]

Perhaps:

1 2 3 4 5

c = myshop_db.execute('''SELECT Orders.Cust_Num, Orders.Order_Date, Orders.Prod_Num, Products.Prod_Name,     Orders.Prod_Qty, Products.Prod_Cost, SUM(Orders.Prod_Qty) * SUM(Products.Prod_Cost) FROM Orders     INNER JOIN Products     ON Orders.Prod_Num = Products.Prod_Num     WHERE Orders.Order_Num = ?''', num)

[dewijones67]

Hello Larz,

Thanks for this, good suggestion, is the problem I now have in the way I am displaying the data. I would like to print out an invoice. Therefore a column of data added up at the bottom.

What I have so far gives me:

Image of original output

When I use the

1	SUM(Orders.Prod_Qty) * SUM(Products.Prod_Cost)

I get:

Image of output following amend using SUM(........)


Here is the code for printing the invoice:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

def ViewInvoice():    myshop_db  = sqlite3.connect("myshop_db.db")    c = myshop_db.cursor()            #inner join tutorial: http://www.sqlitetutorial.net/sqlite-inner-join/    c = myshop_db.execute('''SELECT Orders.Cust_Num, Orders.Order_Date, Orders.Prod_Num, Products.Prod_Name, Orders.Prod_Qty, Products.Prod_Cost,                                                    Orders.Prod_Qty * Products.Prod_Cost as Total                               FROM Orders                               INNER JOIN Products                               ON Orders.Prod_Num = Products.Prod_Num                               WHERE Orders.Order_Num = ?''', num)      rows = c.fetchall()    for row in rows:       tree4.insert("",tk.END, values = row)    myshop_db.commit()    myshop_db.close()   frame4 = Frame(showInvoiceHeaderWin, width=850, height=450, bd=15, relief="raise") tree4= ttk.Treeview(frame4, column=("column1", "column2", "column3", "column4", "column5", "column6", "column7"), show='headings') tree4.heading("#1", text="CUST#") tree4.column("#1", stretch=tk.YES,width = 50, anchor = "n") tree4.heading("#2", text="ORDER DATE") tree4.column("#2", stretch=tk.YES,width = 80, anchor = "n") tree4.heading("#3", text="PRODUCT #") tree4.column("#3", stretch=tk.YES,width = 80, anchor = "n") tree4.heading("#4", text="PRODUCT NAME") tree4.column("#4", stretch=tk.YES,width = 280, anchor = "w") tree4.heading("#5", text="QUANTITY ") tree4.column("#5", stretch=tk.YES,width = 80, anchor = "n") tree4.heading("#6", text="PRODUCT COST") tree4.column("#6", stretch=tk.YES,width = 100, anchor = "w") tree4.heading("#7", text="TOTAL COST") tree4.column("#7", stretch=tk.YES,width = 100, anchor = "w")   frame4.pack() tree4.pack()

As always Larz, thank you in advance for your support,
Dewi

[Larz60+]

you're image came out as text 'image'

[dewijones67]

I have updated the above post with new image links. These should work.
Apologies,
Dewi