Partial fractions in SQL queries

I never thought my maths training would come in handy again. I was working on a financial report, and one of the requirements was to have a particular calculated value show up. The formula didn’t make sense to me, but it was a business/financial logic requirement, so I just dealt with it.

So here’s the core of the problem (specific values had been changed):

select sum(A)/sum(B) - 0.7 from sometable

where “A” and “B” are columns of the database table “sometable”.

So what’s the problem? That select query won’t run. Or at least it didn’t run from a Sybase database (or was it an SQL Server database?). I’m not saying it ran but the value was wrong. I mean it didn’t even execute. Just in case you asked, “A” and “B” are numeric data columns so the sum function will work.

I don’t know how I came up with the idea of using partial fractions. Given that only 0.08%* of staff in the entire office building had maths background, and I probably made up the entire 0.08%, I didn’t have anyone to bounce ideas off of and be told “How about you try using partial fractions on that, Vincent?”

(* a completely made up statistic)

Anyway, I tried using partial fractions, and it worked. Now in partial fractions, you typically deal with decomposing a fraction into 2 or more fractions. Here, we’re combining fractions into 1 fraction. Let me show you.

sum(A)/sum(B) – 0.7
= sum(A)/sum(B) – 7/10
= ( 10*sum(A) – 7*sum(B) ) / 10*sum(B)

If I remember correctly, this (equivalent) SQL query will work:

select ( 10*sum(A) - 7*sum(B) ) / 10*sum(B) from sometable

I’m not a database expert. If you know why that works but not the original (and more direct) version, leave a comment.

[UPDATE: A commenter told me that complicated maths functions don’t work on aggregates. The sum(A) result is an aggregated result. Apparently sum(A)/sum(B) is too complicated. Oh well…]

As part of that same programming task, I had to deal with another similar problem:

select 50 * (sum(A)/sum(B) - 0.7) from sometable

That SQL query also didn’t run. So here’s the partial fraction combining process:

50 * (sum(A)/sum(B) – 0.7)
= 50*sum(A)/sum(B) – 35
= ( 50*sum(A) – 35*sum(B) ) / sum(B)

Alternatives

Now I know there’s another option. I could get sum(A) and sum(B) individually, and then do the required calculation in code (C# code as opposed to database SQL code. I was dealing with ASP.NET then).

After considering my options, I decided to leave all the calculations at the database side. This makes the ASP.NET code “cleaner”. Then I only have to deal with one return value (instead of 2, sum(A) and sum(B)), and I can bind it directly to my database objects for display on the web browser.

Also, there were where (and group-by? Can’t remember…) clauses in the SQL query. I didn’t know if I obtained sum(A) and sum(B) individually (even if they were in the same query) that that will affect their values. I decided to play it safe, and just get it all in one resulting value from the same query.

I didn’t check for efficiency. It wasn’t an oft-used report, so the code execution won’t be run frequently enough to matter.

But if you’re curious enough to do some tests, go ahead. If you then want to share your results, I’d very much appreciate it too.

  1. tham

    select (SumA/SumB – 0.7) from ( select sum(A) as SumA, sum(B) as SumB from sometable group by A,B )

  2. Vincent

    Ahh, sub queries. Well, someone more skilled at database queries will probably do that. I… well… use partial fractions… hahaha.

Comments are closed.