I have an Oracle database I am trying to run report queries against.
I would like to calculate the ratio of a value to the sum of values in the data partition.
The question I have is whether to use the RATIO_TO_REPORT function or the OVER and PARTITION BY functions.
I have tried writing queries both ways and seem to get hung up on the COUNT and calculating percentages.
Any insight is greatly appreciated.
PaulT
--Example Queries
Expand|Select|Wrap|Line Numbers
- SELECT DISTINCT ProductGroup, Product, ProductCount, (ProductCount/TotalCount) AS 'Percentage'
- From (
- Select ProductGroup, Product,
- Sum(subtotal) Over () As 'TotalCount',
- Sum(subtotal) Over (Partition By Product) As 'ProductCount'
- From Table1
- Where Product in ('Product A', 'Product B', 'Product C'))
- Where Product in ('Product A', 'Product B', 'Product C')
- Order by 1 DESC, 2;
Expand|Select|Wrap|Line Numbers
- SELECT ProductGroup, Product, COUNT(Product),
- RATIO_TO_REPORT (SUM(Product))
- OVER (PARTITION BY Product) Percentage
- from Table1
- Where Product in ('ProductA', 'ProductB', 'ProductC')
- Group by ProductGroup, Product;
--Example Database table:
Expand|Select|Wrap|Line Numbers
- ProductGroup Product SerialNum
- Product Group 1 Product A S/N 12345
- Product Group 1 Product A S/N 56789
- Product Group 1 Product B S/N 34567
- Product Group 1 Product B S/N 67890
- Product Group 1 Product B S/N 98765
- Product Group 1 Product C S/N 12312
- Product Group 1 Product C S/N 56756
- Product Group 2 Product E S/N 99999
- Product Group 2 Product E S/N 88888
- Product Group 2 Product F S/N 44444
- Product Group 2 Product F S/N 33333
Expand|Select|Wrap|Line Numbers
- ProductGroup Product Count Percentage
- Product Group 1 Product A 2 .285
- Product Group 1 Product B 3 .428
- Product Group 1 Product C 2 .285
- Product Group 2 Product E 2 .5
- Product Group 2 Product F 2 .5