STATS_MODE at row level — oracle-tech

    Forum Stats

  • 3,701,596 Users
  • 2,239,431 Discussions
  • 7,835,406 Comments

Discussions

STATS_MODE at row level

user5716448user5716448 Posts: 1,674 Silver Badge
edited October 23 in SQL & PL/SQL

Hi,

version 11.2.0.3

Have 2 columns


price and currency and currently uisng stats_mode on each column.

However, can then get price for different currency so need to try use stats_mode at row level.


e.g 2.99 GBP when is actually 2.99 in EUR.


Had hoped to use


stats_mode(retail_price||', '||currency_code) fld


but then just shows ,


If use

stats_mode(retail_price||currency_code)

correctly shows 2.99EUR


How can we use stats_mode with column delimiter?

Thansk

Best Answer

Answers

  • Frank KulashFrank Kulash Moderator Posts: 39,592 Red Diamond

    Hi,

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    See: https://community.oracle.com/tech/apps-infra/kb/articles/4-how-to-ask-questions-in-developer-community-spacesAr

    You want the combination of retail_price and currency_code that occurs most often, right? Note that the retail_price you get may not be the one that occurs most often, and the currency_code you get might also not be the one that occurs most often.

  • mathguymathguy Posts: 9,236 Gold Crown

    Solomon Yakobson gave you an excellent explanation for what you saw happening.

    Beyond that, you need to clarify the problem - both your data model and the desired output. Clearly there are (many) rows where both the price (a number) and the currency code (a string) are NULL. Strictly speaking, that IS the mode of the distribution, unless - as Oracle guesses - you want to ignore those rows. (DO YOU? The answer may be "no", but that is not obvious in all cases.)

    But can you also have a price (a number) with NULL for currency? Or the other way around, - NULL price, but non-NULL currency code (in the same row)? If so, how should those be handled? Solomon proposes that all rows where either the price or the currency code is NULL should be ignored (even if the other column in the same row is not NULL). Again, this may be the correct handling, but IS IT, in your specific business application (of which we know nothing)? If it is, Solomon showed you how to do it.

  • user5716448user5716448 Posts: 1,674 Silver Badge

    Thanks for all replies. If price or currency set then other will be set thus can use Solomon's answer

Sign In or Register to comment.