Python Forum
Python tKinter sQL. How to add a column of values?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python tKinter sQL. How to add a column of values?
#1
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 :)
Reply
#2
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)
Reply
#3
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
Reply
#4
you're image came out as text 'image'
Reply
#5
I have updated the above post with new image links. These should work.
Apologies,
Dewi
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [Tkinter] Tkinter delete values in Entries, when I'm changing the Frame robertoCarlos 11 5,648 Jul-29-2020, 07:13 PM
Last Post: deanhystad
  [Tkinter] Problems to display Web Scraping values in Tkinter Lucas_Ribeiro 0 1,537 May-07-2020, 12:36 AM
Last Post: Lucas_Ribeiro
  [Tkinter] Tkinter adding entry values scratchmyhead 1 2,165 May-04-2020, 05:21 AM
Last Post: Yoriz
  [Tkinter] Unable to Obtain values from one Tkinter Program into another nilaybnrj 1 2,535 Aug-24-2018, 01:24 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020