1

I have a table with a column of building sizes named "SQFT". I trying to find the number of buildings that are within 15% +/- of each rows "SQFT" value. I have tried case, count with filter and window functions but none would give me the results I'm looking for, at least not in the way I was using them.

Below is what I'd like as a final result.

ID   -15%    SQFT    +15%    COUNT
1    85      100     115     2
2    94      110     127     3
3    102     120     138     3
4    111     130     150     4
5    119     140     161     5
6    128     150     173     5
7    136     160     184     5
8    145     170     196     5
9    153     180     207     5
10   162     190     219     5
11   170     200     230     7
12   179     210     242     7
13   187     220     253     7
14   196     230     265     6
15   204     240     276     5
16   213     250     288     4

This is the code so far:

WITH base AS (
   SELECT "Unique ID" as id, "Sqft Total" AS "SQFT", "Sqft Total" * (1-.15) as low, "Sqft Total" * (1+.15) as high
   FROM   "_KEY_TABLE"
   )
SELECT  
    id,
    round(low::numeric,0) as "-15%",
    sqft,
    round(high::numeric,0) as "+15%",
    count(*) over(partition by "SQFT" between low and high) as "COUNT"
    -- case when "SQFT" between low and high is true then 1 end, 
    -- count(*) filter (where "SQFT" between low and high) as counter_filtered,
FROM   base b
group by b."SQFT", id, low, high
  • Provide the sample data and desired result, desired result without sample data is useless. – Evan Carroll Jan 17 '18 at 7:43
  • Evan, thank you for your reply. Yes, I could have been more clear. The desired result is the "COUNT" column. The other columns are the sample data. – Phil_in_Tx Jan 17 '18 at 13:46
0

Not sure exactly what you want, but I imagine it's something like this. Here we use a INNER JOIN and check the right table for the range.

-- sample data
WITH t(x) AS (SELECT generate_series(1,10))

SELECT t1.x,
  t1.x*0.85 AS lower, -- just so you can see the lower
  t1.x*1.15 AS upper, -- just so you can see the upper
  count(*)
FROM t AS t1
INNER JOIN t AS t2
  ON t2.x BETWEEN t1.x*0.85 AND t1.x*1.15  -- JOY
GROUP BY t1.x
ORDER BY t1.x;

Output,

 x  | lower | upper | count 
----+-------+-------+-------
  1 |  0.85 |  1.15 |     1
  2 |  1.70 |  2.30 |     1
  3 |  2.55 |  3.45 |     1
  4 |  3.40 |  4.60 |     1
  5 |  4.25 |  5.75 |     1
  6 |  5.10 |  6.90 |     1
  7 |  5.95 |  8.05 |     3
  8 |  6.80 |  9.20 |     3
  9 |  7.65 | 10.35 |     3
 10 |  8.50 | 11.50 |     2
(10 rows)
  • Thank you Evan and McNets, both of your solutions worked perfectly. Alas, I can only choose one correct answer. – Phil_in_Tx Jan 18 '18 at 0:03
0
create table buildings (Id int, Sqft int);

insert into buildings values
(1,  100), (2,  110), (3,  120), (4,  130), (5,  140), (6,  150), (7,  160), (8,  170), 
(9,  180), (10, 190), (11, 200), (12, 210), (13, 220), (14, 230), (15, 240), (16, 250);
select Id, 
       Sqft * (1 - .15) as low,
       Sqft,
       Sqft * (1 + .15) as high,
       (select count(*) 
        from   buildings t2
        where  t2.Sqft between (t.Sqft * (1 - .15)) and  (t.Sqft * (1 + .15))) as "count"
from   buildings t
id |    low | sqft |   high | count
-: | -----: | ---: | -----: | ----:
 1 |  85.00 |  100 | 115.00 |     2
 2 |  93.50 |  110 | 126.50 |     3
 3 | 102.00 |  120 | 138.00 |     3
 4 | 110.50 |  130 | 149.50 |     3
 5 | 119.00 |  140 | 161.00 |     5
 6 | 127.50 |  150 | 172.50 |     5
 7 | 136.00 |  160 | 184.00 |     5
 8 | 144.50 |  170 | 195.50 |     5
 9 | 153.00 |  180 | 207.00 |     5
10 | 161.50 |  190 | 218.50 |     5
11 | 170.00 |  200 | 230.00 |     7
12 | 178.50 |  210 | 241.50 |     7
13 | 187.00 |  220 | 253.00 |     7
14 | 195.50 |  230 | 264.50 |     6
15 | 204.00 |  240 | 276.00 |     5
16 | 212.50 |  250 | 287.50 |     4

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.