Sum of Results Python SQL Query

Question

Current code:

 import pyodbc

connection = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

if connection:
        print("Yes we connected\n")

cur = connection.cursor()

cur.execute(SELECT cost1 FROM tbl)

data = cur.fetchall()

for row in data:
        print(row)

I admit I have almost no idea what I am doing in python and I got slapped with a project a little while ago that is do ASA-yesterday. I need to be able to take the results from the SQL query and sum them together. Or store them in variables to where I can call back to them in an equation.

Results are: 10, 431, 543, 1268, 1207

Expected Result: 3459

I am more than happy to give any more information, and I will be spending the interim continuing to research.

you can sum the row values if they integer if not convert them to integer before the addition — mehmetö
– mehmetö, Commented Mar 19, 2018 at 20:03
They are number values, they actually have decimals. I just truncated them for the example. Can I still sum the rows if they are numbers values? And is it just print(sum(row))? — RBC Kyle Bullard
– RBC Kyle Bullard, Commented Mar 19, 2018 at 20:06
No, data is a list which contains numbers, row is just one of those numbers so you can't use sum(row). You can add row values to a variable one-by-one but you can use sum function for data — mehmetö
– mehmetö, Commented Mar 19, 2018 at 20:09

Alan Hoover · Accepted Answer · 2018-03-19 20:13:11Z

3

If you are only after the sum and not the individual numbers then the easiest way is to do that in SQL:

query = 'select sum(cost1) from tbl'

If you want the individual numbers also then.:

cur.execute(SELECT cost1 FROM tbl)

data = cur.fetchall()
tot = 0
for row in data:
        print(row)
        tot += row[0]
print(tot)

answered Mar 19, 2018 at 20:13

Alan Hoover

1,4502 gold badges9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RBC Kyle Bullard Over a year ago

Thank you so much! I was not able to sum it in SQL as it is a much bigger query than shown. Your second result works like a charm.

Fabio · Accepted Answer · 2018-03-19 20:13:28Z

1

your SQL query should be modified to SELECT sum(cost1) FROM tbl

or you can get the results in the python, and sum them... otherwise you are just getting a list of values in the column cost1

or in python you can do:

import pyodbc

connection = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

if connection:
        print("Yes we connected\n")

cur = connection.cursor()

cur.execute(SELECT cost1 FROM tbl)

data = cur.fetchall()
sum = 0
for row in data:
        sum = sum + row
print(sum)

edited Mar 19, 2018 at 20:13

answered Mar 19, 2018 at 20:10

Fabio

311 silver badge8 bronze badges

2 Comments

RBC Kyle Bullard Over a year ago

The SQL query is actually massive, and I can't sum it there unfortunately. I've been doing some research and cannot find a solution the works for summing the columns in python.

Fabio Over a year ago

added a pythonic solution for the sum as well

user3103059 · Accepted Answer · 2019-01-26 12:27:38Z

0

You don't need to go adding row by row:

import pyodbc
connection = pyodbc.connect('DRIVER={ODBC Driver 13 for SQLServer};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

if connection:
    print("Yes we connected\n")

cur = connection.cursor()
cur.execute('SELECT sum(cost1) FROM tbl;')
x=cur.fetchone()[0]
print(x) # x will hold the value of the sum

Have a great day.

answered Jan 26, 2019 at 12:27

user3103059

Collectives™ on Stack Overflow

Sum of Results Python SQL Query

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related