CODEWITHSUNDEEP
sqlpostgresqlpercentagepostgresql-9.3
newbie_girl
newbie_girl

Reputation: 353

How to return percentage in PostgreSQL?

CREATE TEMPORARY TABLE

CREATE TEMP TABLE percentage(
  gid SERIAL,
  zoom smallint NOT NULL,
  x smallint NOT NULL,
  y smallint NOT NULL
);

INSERT DATA

INSERT INTO percentage(zoom, x, y) VALUES
(0,5,20), 
(0,5,21), (0,5,21),
(0,5,22), (0,5,22), (0,5,22),
(0,5,23), (0,5,23), (0,5,23), (0,5,23),
(0,5,24), (0,5,24), (0,5,24), (0,5,24), (0,5,24),
(1,5,20), 
(1,5,21), (1,5,21),
(1,5,22), (1,5,22), (1,5,22),
(1,5,23), (1,5,23), (1,5,23), (1,5,23),
(1,5,24), (1,5,24), (1,5,24), (1,5,24), (1,5,24);

How many times certain tile shows up (tile is represented by x and y)

SELECT zoom, x, y, count(*) AS amount 
FROM percentage 
GROUP BY zoom,x,y 
ORDER BY zoom, amount;

Result:

 zoom | x | y  | amount 
------+---+----+--------
    0 | 5 | 20 |      1
    0 | 5 | 21 |      2
    0 | 5 | 22 |      3
    0 | 5 | 23 |      4
    0 | 5 | 24 |      5
    1 | 5 | 20 |      1
    1 | 5 | 21 |      2
    1 | 5 | 22 |      3
    1 | 5 | 23 |      4
    1 | 5 | 24 |      5
(10 rows)

Question

How to get back percentage of each tile (x and y) for certain zoom, or in other words, how many times have the certain tile showed up for certain zoom?

Wanted result:

 zoom | x | y  | amount | percentage
------+---+----+--------+-----------
    0 | 5 | 20 |      1 |     6.667
    0 | 5 | 21 |      2 |    13.333
    0 | 5 | 22 |      3 |        20
    0 | 5 | 23 |      4 |    26.667
    0 | 5 | 24 |      5 |    33.333
    1 | 5 | 20 |      1 |     6.667
    1 | 5 | 21 |      2 |    13.333
    1 | 5 | 22 |      3 |        20
    1 | 5 | 23 |      4 |    26.667
    1 | 5 | 24 |      5 |    33.333
(10 rows)

*This is just a sample data, percentages are not supposed to be the same, except as a pure coincidence!

Upvotes: 0

Views: 2069

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

If am not wrong you are looking for this 

SELECT zoom,x,y,
       amount,
       ( amount / Cast(Sum(amount) OVER(partition BY zoom) AS FLOAT) ) * 100 as amt_percentage
FROM   (SELECT zoom,x, y,
               Count(*) AS amount
        FROM   percentage
        GROUP  BY zoom,x,y) a

Or even 

SELECT zoom,x,y,
       Count(*) AS amount,
       ( Count(*) / Cast(Sum(Count(*))OVER(partition BY zoom) AS FLOAT) ) * 100 AS amt_percentage
FROM   percentage
GROUP  BY zoom,x,y

Casting the denominator to FLOAT is avoid the Integer division

Upvotes: 2

Related Questions