Обсуждение: How to compute percentages

Поиск
Список
Период
Сортировка

How to compute percentages

От
Sindile Bidla
Дата:
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


Re: How to compute percentages

От
Humberto Carvalho
Дата:
Try something like this

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





--
Humberto Carvalho

Re: How to compute percentages

От
"Oliveiros d'Azevedo Cristina"
Дата:
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 -----
Sent: Wednesday, May 23, 2012 12:23 PM
Subject: [NOVICE] 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