Обсуждение: How to compute percentages
I have a query that is like this:
SELECT cl1, count(cl2) as no_accounts, sum(cl2) as consumption
FROM tbl1
WHERE cl2 != 0
GROUP BY cl1
ORDER BY cl1;
this gives me the following output
cl1 no_accounts consumption
a 2 50
b 1 100
c 5 30
On the same query i want to calculate percentages no_accounts and consumption without having to create a table or view and then computing the percentages
cl1 no_accounts percent_no_accounts consumption percent_no_consumption
a 2 25 50 27
b 1 12 100 55
c 5 62 30 16
Try something like this
--
Humberto Carvalho
select acl1, no_accounts , Round(100*counter/total,2) as percentage
from (select count(1) as no_accounts , cl1
from xpto t
where xxxx) x ,
(select count(1) as total
from abc t
where xxxxx) y;
Best regards,
HC
On 23 May 2012 12:23, Sindile Bidla <sindile.bidla@gmail.com> wrote:
I have a query that is like this:SELECT cl1, count(cl2) as no_accounts, sum(cl2) as consumptionFROM tbl1WHERE cl2 != 0GROUP BY cl1ORDER BY cl1;this gives me the following outputcl1 no_accounts consumptiona 2 50b 1 100c 5 30On the same query i want to calculate percentages no_accounts and consumption without having to create a table or view and then computing the percentagescl1 no_accounts percent_no_accounts consumption percent_no_consumptiona 2 25 50 27b 1 12 100 55c 5 62 30 16
Humberto Carvalho
Hi,
Sindile,
Could you please try this variation of your own query, please?
Best,
Oliver
SELECT cl1, count(cl2) as no_accounts,
count(cl2) * 100.0 / (SELECT count(cl2) FROM tbl1) as percent_no_accounts,
sum(cl2) as consumption,
sum(cl2) * 100.0 / (SELECT sum(cl2) FROM tbl1) as percent_no_consumption
FROM tbl1
WHERE cl2 != 0
GROUP BY cl1
ORDER BY cl1;
----- Original Message -----From: Sindile BidlaTo: pgsql-noviceSent: Wednesday, May 23, 2012 12:23 PMSubject: [NOVICE] How to compute percentagesI have a query that is like this:SELECT cl1, count(cl2) as no_accounts, sum(cl2) as consumptionFROM tbl1WHERE cl2 != 0GROUP BY cl1ORDER BY cl1;this gives me the following outputcl1 no_accounts consumptiona 2 50b 1 100c 5 30On the same query i want to calculate percentages no_accounts and consumption without having to create a table or view and then computing the percentagescl1 no_accounts percent_no_accounts consumption percent_no_consumptiona 2 25 50 27b 1 12 100 55c 5 62 30 16