Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Weighted percentiles, part 2

From this table:

```SELECT    item_id
,    units
,    price
FROM      items
ORDER BY  price
;

ITEM_ID      UNITS      PRICE
--------- ---------- ----------
1          4       1.99
3          6       2.29
4         11       2.49
2          1       2.99
...
```

...and so on, I can find the average price, min, max, etc. What I need to know is the fifth percentile and the 95th percentile of the price data, weighted by units. I have noticed that there are functions like percentile_disc. But my problem is that I have to weight the price by the units sold.

Do you have any suggestions in Oracle SQL?

What if you want two (or more) such results on the same result-set row? Instead of having a WHERE clause that chooses one row (as in Query 2) you can apply the same logic to computing a new column that has a non-NULL value in only one row.

```SELECT  run.*         -- Query 3
,       CASE
WHEN  .05 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN  price
END   AS price_05
,       CASE
WHEN  .95 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN  price
END   AS price_95
FROM    (    -- Begin sub-query "run", same as query 1
SELECT  item_id
,  units
,  price
,  SUM (units)
OVER (ORDER BY price
RANGE UNBOUNDED PRECEDING)
AS running_total
,  (
SELECT  SUM (units)
FROM    items
)  AS grand_total
FROM    items
)  run
;

ITEM                RUNNING   GRAND  PRICE  PRICE
ID  UNITS  PRICE    TOTAL   TOTAL     05     95
----- ------ ------ -------- ------- ------ ------
1      4   1.99        4      22   1.99
3      6   2.29       10      22
4     11   2.49       21      22          2.49
2      1   2.99       22      22
```

We can SELECT from the result set of Query 3 to get both percentile values, and the other statistics, at the same time.

```SELECT  MIN (price)     AS price_min     -- Query 4 (final answer)
,  MAX (price_05)  AS price_05
,  SUM (units * price) / SUM (units)
AS price_avg
,  MAX (price_95)  AS price_95
,  MAX (price)     AS price_max
FROM    (  -- Begin sub-query "run", same as query 3
SELECT  run.*
,       CASE
WHEN  .05 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN  price
END   AS price_05
,       CASE
WHEN  .95 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN  price
END   AS price_95
FROM    (  -- Begin sub-query 4b, same as query 1
SELECT  item_id
,  units
,  price
,  SUM (units)
OVER (ORDER BY price
RANGE UNBOUNDED PRECEDING)
AS running_total
,  (
SELECT  SUM (units)
FROM    items
)  AS grand_total
FROM    items
)  run
)  -- End sub-query 4a
;
```

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Lessons learned from Credit Karma GraphQL architecture

Credit Karma's vice president of engineering explains why and how the personal finance service is using the GraphQL data query ...

• ### EnterpriseDB looks to grow market for PostgreSQL

Enterprises are increasingly using the open source PostgreSQL database. Read in this Q&A where the growth is coming from and how ...

• ### 3 of the top use cases for graph databases

Graph databases establish many unique relationships between data points. These unusual relationships are beneficial in many use ...

• ### Embedded BI software creates common ground for diverse analytics

Learn how embedding separate business intelligence capabilities into one application empowers users to drill down, access and ...

• ### Embedded BI and analytics apps speed workflows, insight access

Embedded BI is fast becoming a focal point for analytics uses as data analysts, developers and business users enjoy faster ...

• ### What Salesforce means for Tableau in the cloud

After the Salesforce acquisition, users have wondered about the future of Tableau in the cloud. Experts weigh in on how the ...

## SearchSAP

• ### Understand SAP ECC vs. HANA vs. S/4HANA vs. R/3

SAP ECC, HANA, S/4HANA and R/3 are all valuable tools in the IT world, but it's easy to get them confused. Learn about their ...

• ### 5 tips for a successful S/4HANA Cloud implementation

Moving to S/4HANA public cloud can help you save on IT maintenance and simplify real-time analysis. Here's a look at what you can...

• ### Take care of data before SAP S/4HANA migration

In this Q&A, Rajesh Rengarethinam of ERP security vendor Appsian discusses why reviewing data security and business processes are...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### CSS performance optimization in 5 easy steps

Here are five CSS performance tips to help you avoid website performance problems.

• ### Why the 8 Java primitive data types are not objects

What's the difference between Java primitive types and objects? To start, don't classify primitive types as objects. Let's ...

• ### A brief history of Java: How it forever changed programming

The Java programming language is an important tool in an application developer's quiver. But Java has also undergone many changes...

## SearchDataCenter

• ### Plan for a successful ITIL implementation

Corporate buy-in and training are essential for organizations that decide to adopt the ITIL framework. Admins should also help ...

• ### Learn the main Linux OS components

Linux is one operating choice to run on your infrastructure. Get started with these terms to discover how the OS works and how it...

• ### New Dell EMC PowerEdge servers are built for the rugged edge

New Dell Streaming Media Platform includes two small form factor PowerEdge servers and a Modular Data Center. Dell aims the ...

## SearchContentManagement

With new Google Drive integration, Adobe embeds free features for commenting and annotating PDFs inside Google Docs. Acrobat ...

• ### 4 benefits of business process automation

Companies are automating business processes to improve workflows and use technology effectively. Some benefits of BPA include ...

• ### Test yourself on the differences between SharePoint and OneDrive

Businesses use Microsoft SharePoint and OneDrive capabilities for a number of reasons. Test your knowledge on the differences ...

## SearchHRSoftware

• ### Trump's H-1B work visa actions disappoint ex-Disney workers

President Donald Trump arrived in office promising to reform the H-1B visa program. But his actions have fallen short of what ...

• ### How real-time analytics can benefit HR

For analytics to be truly useful, it must be embedded into user workflows. Learn how HR tools are incorporating such approaches ...

• ### Human vs. AI in recruiting: Why both matter

Recruiters may consider using both the human element and the data brought in by intelligent tools if it can help them make better...

Close