Python Forum

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:

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 :)

Perhaps:

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)

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

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:

   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

you're image came out as text 'image'

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