More efficient way to do a SQL query?

Question

Is there a better way to do the following calculation of sales - returns = total?

exchange_rates = self.rates_dict

self.DBConnection.cursor.execute("SELECT royalty_price, royalty_currency FROM sales_raw WHERE provider='%s' AND sales_or_return = 'S' AND date='%s'"%(provider, self.date))
data = list(self.DBConnection.cursor.fetchall())
sales_in_usd = sum(float(price) * exchange_rates[currency] for price, currency in data)

self.DBConnection.cursor.execute("SELECT royalty_price, royalty_currency FROM sales_raw WHERE provider='%s' AND sales_or_return = 'R' AND date='%s'"%(provider, self.date))
data = list(self.DBConnection.cursor.fetchall())
returns_in_usd = sum(float(price) * exchange_rates[currency] for price, currency in data)

total_in_usd = sales_in_usd - returns_in_usd

SELECT SUM(royalty_price), SUM(royalty_currency) FROM sales_raw WHERE.... — bumperbox
– bumperbox, Commented May 14, 2012 at 22:03
Why.... WHY do you use the % operator? NEVER use it for SQL queries. It enables SQL injection. use WHERE field = %s without quotes and pass the list of params as a second argument to the execute function! — ThiefMaster
– ThiefMaster, Commented May 14, 2012 at 22:11
I think this is very similar to your other question: stackoverflow.com/questions/10507577/… The only apparent difference is you want to subtract the results of two subselects. And, yes, you can do this in SQL. — Mark Byers
– Mark Byers, Commented May 14, 2012 at 22:16
Every dbapi implementation needs to support params. %s might be wrong but using the % operator is always wrong. — ThiefMaster
– ThiefMaster, Commented May 14, 2012 at 22:18
Having said all of that, @ThiefMaster, OP is doing it wrong, so good catch :) I deleted my comment string since I ultimately see what you were saying and I just blabbered on basically supporting your argument. My apologies. — swasheck
– swasheck, Commented May 14, 2012 at 22:41

swasheck · Accepted Answer · 2012-05-14 22:33:11Z

4

SELECT    
    royalty_currency,
    SUM (
          CASE sales_or_return 
          when 'R' then royalty_price * -1
          else royalty_price
       ) as TotalSales
  WHERE provider=%s AND date = %s AND sales_or_return IN ('S', 'R')
    GROUP BY royalty_currency

I'm not 100% sure this will compile on MySQL try it first. If it does ... great ... if not, let me know since I have another idea. Once you get this, multiply by the exchange rate for that given currency.

EDIT: added AND sales_or_return IN ('S', 'R') per @ypercube's recommendation. Additionally, this assumes that all currency is the same. You will still need to iterate the results according to their exchange rates. If the exchange rates are stored in a database then you could join the data on the exchange rate table. Not being a great accountant, I don't know if you'd want to capture the exchange rate at time of sale or if everything is calculated as of current exchange rates. My assumption is that, at this point, you're calculating sales against current exchange rates that you're obtaining via a web service.

An alternative query could be:

SELECT sales.royalty_currency, sales.GrossSales, returns.GrossReturns, sales.GrossSales - returns.GrossReturns, 
(SELECT sum(royalty_price) GrossSales, royalty_currency 
    FROM sales_raw 
    WHERE provider=%s AND sales_or_return = 'S' AND date=%s) sales
JOIN 
(SELECT sum(royalty_price) GrossReturns, royalty_currency 
    FROM sales_raw 
    WHERE provider=%s AND sales_or_return = 'R' AND date=%s) returns
on sales.royalty_currency = returns.royalty_currency

This one could probably be refined a bit more, also. It also gets you some other information that might be useful for other reporting purposes.

edited May 14, 2012 at 22:33

answered May 14, 2012 at 22:09

swasheck

4,7232 gold badges32 silver badges58 bronze badges

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

3 Comments

ypercubeᵀᴹ Over a year ago

You also need AND sales_or_return IN ('S', 'R') in the Where clause.

Charles Duffy Over a year ago

The %s references shouldn't be quoted -- they should be used unquoted, and the database driver itself (not Python's string formatting operators!) should be used to substitute actual values. (Some other DB-API drivers require ? in place of %s -- check the docs, or use a layer such as SQLAlchemy's query builder which abstracts these details away). Doing this the wrong way can result in SQL injection attacks.

swasheck Over a year ago

@CharlesDuffy Indeed. MySQLdb and psycopg2 both accept %s. I'm personally more comfortable with the ? but have grown accustomed to tossing in %s as long as it's formatted properly within the context of a db connection.

ChristopheD · Accepted Answer · 2012-05-14 22:02:33Z

3

Sure, let the database do the work (by using sum directly in your sql statement).

EDIT: You can group by currency type to have one total per currency type.

answered May 14, 2012 at 22:02

ChristopheD

117k30 gold badges167 silver badges182 bronze badges

7 Comments

Mark Byers Over a year ago

Might be more difficult than just using SUM if he doesn't have the exchange rates in the database.

Scott Hunter Over a year ago

Could group them by currency, so at least there would be less work to do after the query.

swasheck Over a year ago

Math says that all he has to do is get the base difference of the sum and then multiply by the rates.

ChristopheD Over a year ago

@Mark Byers: true remark, but at this point the question is a little underspecified I guess. You could use a simple group by to combine values of the same currency then.

swasheck Over a year ago

I just realized that perhaps each sale could be at a different exchange rate. That complicates things.

|

vasek1 · Accepted Answer · 2012-05-14 22:17:01Z

Not the least lines of code, but intuitive and readable, can be condensed to skip steps if needed

Put the exchange rates in your database. Lets assume its in the database with columns royalty_currency and exrate
Join the exchange rates table with the sales_raw table

(SELECT sales_raw.provider, sales_raw.royalty_price, sales_raw.royalty_currency, sales_raw.sales_or_return, exchange_rates.exrate FROM sales_raw INNER JOIN exchange_rates ON sales_raw.royalty_currency = exchange_rates.royalty_currency) sales
Pivot up by sales and returns

(SELECT provider, royalty_currency, exrate CASE sales_or_return WHEN 'S' THEN royalty_price ELSE 0 AS royalty_sales CASE sales_or_return WHEN 'R' THEN royalty_price ELSE 0 AS royalty_returns FROM sales) pivoted_sales
Calculate the total

SELECT SUM(royalty_sales * exrate) - SUM(royalty_returns * exrate) AS total_sum FROM pivoted_sales

Collectives™ on Stack Overflow

More efficient way to do a SQL query?

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related