newbie_girl - 1 year ago 112
SQL Question

# 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!

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download