CODEWITHSUNDEEP
sqlpostgresql
user3422859
user3422859

Reputation:

Division in select query in postgresql

Below given if my table

create table batches ( batchid int,free smallint,qty int)


INSERT INTO batches VALUES (2329, 0, 100);
INSERT INTO batches VALUES (2329, 1, 10);
INSERT INTO batches VALUES (2331, 0, 75);
INSERT INTO batches VALUES (2331, 1, 4);

this table will return

 batchid free qty 
 2329    0    100
 2329    1    10
 2331    0    75
 2331    1    4


need to perform division on column qty
I.e in this case 100 should divide with 10 (batchid should be same ) 100/10 (here 10 is the qty where free = 1)

EXPECTED RESULT

Batchid  freelimit
2329     10
2331     18

Upvotes: 2

Views: 1718

Answers (2)

Vivek S.
Vivek S.

Reputation: 21963

WITH Queries (Common Table Expressions)

with a as (
    select 
      batchid,qty 
    from batches  
    where  free = 0 order by batchid 
)
, b as (
    select 
      batchid,qty 
    from batches  
    where  free = 1order by batchid 
)
select 
  batchid, floor((a.qty/b.qty))::real as freelimit 
from a inner join b using(batchid)

SQLFIDDLE-DEMO

Upvotes: 1

vyegorov
vyegorov

Reputation: 22915

You want a self-join here:

WITH d(batchid, free, qty) AS (
    VALUES (2329::int, 0::int2, 100::int), (2329, 1, 10),
           (2331, 0, 75),  (2331, 1, 4)
)
SELECT a.batchid, a.qty/b.qty
  FROM d a
  JOIN d b ON a.batchid=b.batchid AND b.free=1
 WHERE a.free=0;

Upvotes: 0

Related Questions