CODEWITHSUNDEEP
sqlpostgresql
Padagomez
Padagomez

Reputation: 1254

How do you divide in the select statement?

I'm trying to get the quotient of two columns inside the select statement. Here's what I have tried. 

Select ( select sum(x.amount) amount
         from this_table x
         where x.acct_no = '52'
       ) / 
       ( select sum(x.amount) amount
         from this_table x
         where x.acct_no = '53'
       ) as amount
from this_table y
join this_table x on x.acct_no = y.acct_no
where x.acct_no = '52' or x.acct_no = '53'

When I do this, my amount column just comes out as 1, and I'm positive that isn't what the result should be. Any advice or help? Solution?

Upvotes: 0

Views: 539

Answers (5)

AjV Jsy
AjV Jsy

Reputation: 6095

FWIW I just had a play in SQLFiddle http://sqlfiddle.com/#!1/d41d8/1606 , and if you're only after a single row single column result, PostgreSQL seems to allow :

WITH Amounts52 as (select   23 as sum52),
     Amounts53 as (select 4356 as sum53)
SELECT Amounts52.sum52::float
     / Amounts53.sum53  AS Result
FROM Amounts52, Amounts53

So you can put your Sum() queries in the two Common Table Expressions and get a the single result - if this approach appeals to you (it has its uses)

Of course it is a lot simpler to use this ( http://sqlfiddle.com/#!1/d41d8/1924/0 ) : 

SELECT (select   23 as sum52)
      /(select 4356 as sum53)::float AS Answer

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659017

The basic statement would be:

SELECT 
  (SELECT sum(amount)::numeric   FROM this_table WHERE acct_no = '52')  /
  (SELECT NULLIF(sum(amount), 0) FROM this_table WHERE acct_no = '53') AS div

  • NULLIF prevents division by 0. You get NULL in this case, which is the appropriate result.

  • If amount is some kind of integer, cast to a numeric type with more precision to preserve fractional digits. (You may want round().) One cast is enough, the result is in the data-type with the greater precision. Relevance dependings on your actual types.

  • A single query with CASE statements like @Brian provided may be faster. With an index it hardly matters.

  • Am index on (acct_no) makes this fast, a multicolumn index on (acct_no, amount) even faster, if index-only scans (Postgres 9.2+) are possible.

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74365

This ought to do you:

select ratio = sum(cast( case t.acct_no when '52' then t.amount else 0.00 end as numeric(15,2) )
             / sum(cast( case t.acct-no when '53' then t.amount else 0.00 end as numeric(15,2) )
from this_table t
where t.acct_no in ( '52' , '53' )

You might not need the cast if amount is a suitable data type.

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

This should do as you want:

select  sum(case when acct_no = '52' then amount else 0 end)
      / sum(case when acct_no = '53' then amount else 0 end) as amount
from    this_table
where   acct_no in ('52','53')

To make the amount column show zero if the divisor is zero (rather than return an error), you can use:

select  case when sum(case when acct_no = '53' then amount else 0 end) = 0 then 0
        else
        sum(case when acct_no = '52' then amount else 0 end)
      / sum(case when acct_no = '53' then amount else 0 end) end as amount
from    this_table
where   acct_no in ('52','53')

May be useful if you plan on doing this often and/or using the results in an application where you don't want an error thrown back at you.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125504

Cast to numeric

select 3 / 2;
 ?column? 
----------
        1

select 3::numeric / 2;
      ?column?      
--------------------
 1.5000000000000000

Upvotes: 1

Related Questions