Forum Stats

  • 3,757,045 Users
  • 2,251,189 Discussions
  • 7,869,702 Comments

Discussions

STATS_MODE at row level

user5716448
user5716448 Member Posts: 1,737 Silver Badge
edited Oct 23, 2020 7:41PM 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 Kulash
    Frank Kulash Member, Moderator Posts: 41,026 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.

  • mathguy
    mathguy Member Posts: 10,063 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.

  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge

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