Oracle: how to get percent of total by a query?
count(1) is equal to sum(1) in your case.
Try something like this:
18:39:36 SYSTEM@dwal> ed
Wrote file S:\\tools\buffer.sql
1 select owner,
2 count(*) group_cnt,
3 sum(count(*)) over() total_cnt,
4 round(100*(count(*) / sum(count(*)) over ()),2) perc
5 from dba_objects
6 group by owner
7* order by 4 desc
18:39:57 SYSTEM@dwal> /
OWNER GROUP_CNT TOTAL_CNT PERC
------------------------------ ---------- ---------- ----------
SYS 31609 59064 53.52
PUBLIC 24144 59064 40.88
XDB 1660 59064 2.81
SYSTEM 597 59064 1.01
WMSYS 332 59064 .56
EXFSYS 312 59064 .53
IRKAZDATA 158 59064 .27
STRMADMIN 92 59064 .16
DBSNMP 55 59064 .09
RI 25 59064 .04
PASS 16 59064 .03
POTS 19 59064 .03
TI 11 59064 .02
STRMODS 11 59064 .02
OUTLN 10 59064 .02
APPQOSSYS 5 59064 .01
ORACLE_OCM 8 59064 .01
17 rows selected.
Elapsed: 00:00:00.16
update: or even simplier with ratio_to_report
18:53:36 SYSTEM@dwal> ed
Wrote file S:\\tools\buffer.sql
1 select owner,
2 round(100*ratio_to_report(count(*)) over (), 2) perc
3 from dba_objects
4 group by owner
5* order by 2 desc
18:54:03 SYSTEM@dwal> /
OWNER PERC
------------------------------ ----------
SYS 53.52
PUBLIC 40.88
XDB 2.81
SYSTEM 1.01
WMSYS .56
EXFSYS .53
IRKAZDATA .27
STRMADMIN .16
DBSNMP .09
RI .04
PASS .03
POTS .03
TI .02
STRMODS .02
OUTLN .02
APPQOSSYS .01
ORACLE_OCM .01
17 rows selected.
Elapsed: 00:00:00.20
How to calculate percentage in oracle sql
If you want a result like 66.6% rather than 66.7%, you would use trunc() rather than round() (although the latter is probably better). And you need to round a/b to three decimal places, so there is one left after you multiply by 100.
Then, you can have both counts in one query, and you can add the percentage calculation also in the same query.
select count(case when propkey = 0 then 1 end) countid0,
count(propkey) totalidcount,
source,
to_char(round(count(case when properkey = 0 then 1 end)/count(properkey), 3)*100)
|| '%' percentageids0
from......
Oracle SQL to calculate percentage
If I understand correctly, you want aggregation with a window function to calculate the total for the subject:
select subject, grade, count(*) as total,
100.0 * count(*) / sum(count(*)) over (partition by subject)
from t
group by subject, grade
SqlFiddle
Calculate percentages of columns in Oracle SQL
Use count, sum and case expressions, together with basic arithmetic operators +,/,*
COUNT(*)gives a total count of people in the tableSUM(column)gives a sum of 1 in given column- case expressions make possible to implement more complex conditions
The common pattern is X / COUNT(*) * 100 which is used to calculate a percent of given value ( val / total * 100% )
An example:
SELECT
-- percentage of people that have 1 in marketing_campaign column
SUM( marketing_campaign ) / COUNT(*) * 100 As marketing_campaign_percent,
-- percentage of people that have 1 in sales column
SUM( sales ) / COUNT(*) * 100 As sales_percent,
-- complex condition:
-- percentage of people (one person is one row/ id) who have a 1
-- in the first column and a 1 in the second or third column
COUNT(
CASE WHEN marketing_campaign = 1
AND ( personal_campaign = 1 OR sales = 1 )
THEN 1 END
) / COUNT(*) * 100 As complex_condition_percent
FROM table;
ORACLE query with percentage of total for categories and subcategories
you could use a join
select a.Year, a.Month, a.Sector, a.Subsector, a.sum_employed
, (a.sum_employed/b.tot_employed)*100, a.sum_unemployed, (a.sum_unemployed/b.tot_unemployed)*100
from (
SELECT Year, Month,Sector, Subsector, sum(employed) sum_employed, sum(unemployed) sum_unemployed
FROM dbo.workforce
where Year= 2017 and Month = 12 and Sector = any('0700','0500','0600')
group by Year, Month,Sector, Subsector
) a
inner join (
SELECT Year, Month,Sector,sum(employed) tot_employed, sum(unemployed) tot_unemployed
FROM dbo.workforce
where Year= 2017 and Month = 12 and Sector = any('0700','0500','0600')
group by Year, Month,Sector
) b on a.Year = b.Year
and a.Month = b.Month
and a.Sector = b.Sector
Calculate percentage in SQL Oracle
Use SUM as an analytic function:
SELECT id,
day,
100 * total_per_day / SUM(total_per_day) OVER (PARTITION BY id)
AS percentage
FROM table_name
Which, for the sample data:
CREATE TABLE table_name (ID, DAY, total_per_day) AS
SELECT 1, 'weekday', 78 FROM DUAL UNION ALL
SELECT 1, 'weekend', 20 FROM DUAL UNION ALL
SELECT 2, 'weekday', 13 FROM DUAL UNION ALL
SELECT 2, 'weekend', 37 FROM DUAL;
Outputs:
ID DAY PERCENTAGE 1 weekday 79.59183673469387755102040816326530612245 1 weekend 20.40816326530612244897959183673469387755 2 weekday 26 2 weekend 74
Related Topics
SQL Server Script to Create a New User
MySQL Auto-Store Datetime for Each Row
Inserting Text String with Hex into Postgresql as a Bytea
Difference Between Int Primary Key and Integer Primary Key SQLite
After Installing SQL Server 2014 Express Can't Find Local Db
Exec Failed Because the Name Not a Valid Identifier
How to Create an "On-The-Fly" Mapping Table Within a Select Statement in Postgresql
Do We Need to Specify "Not Null" for Primary Key? Oracle/Sql
Generate SQL to Update Primary Key
Reverse String Word by Word Using SQL
Is a One Column Table Good Design
How Do We Implement an Is-A Relationship
Concatenate Multiple Rows in an Array with SQL on Postgresql
SQL Query That Groups Different Items into Buckets
Best Way to Compare Dates Without Time in SQL Server
Command for Adding a Default Constraint