CODEWITHSUNDEEP
sqldatabaseoracle11g
Wuschelbeutel Kartoffelhuhn
Wuschelbeutel Kartoffelhuhn

Reputation: 1958

In Oracle SQL, how do you query the proportion of records of a certain value?

Say, you have a query like

SELECT COUNT(*), date FROM ORDERS GROUP BY date ORDER BY date

but you also want to have a third "phantom/dummy field", where it basically tells you the fraction of orders each day that are of a particular type (lets say "Utensils" and "Perishables").

I should say that there is an additional column in the ORDERS table that has the type of the order: 

order_type

The third dummy column should do something like take the count of orders on a date that have the "Utensils" or the "Perishables" type (not XOR), then divide by the total count of orders of that day, and then round to 2 decimal points, and append a percentage sign.

The last few formatting things, aren't really important...all I really need to know is how to apply the logic in valid PLSQL syntax.

Example output

4030 2012-02-02 34.43%
4953 2012-02-03 16.66%

Upvotes: 1

Views: 788

Answers (2)

Danny Varod
Danny Varod

Reputation: 18069

To Add sum of orders of same type to query:

select
    o.*,
    (
        select count(o2.OrderType)
        from ORDERS o2
        where o2.OrderType = o.OrderType
    ) as NumberOfOrdersOfThisType
from ORDERS o

To Add fraction of orders of same type to query:
(Check variable definition to make sure it is PL/SQL)

declare totalCount number

select count(*)
into totalCount
from ORDERS

select
    o.*,
    (
        select count(o2.OrderType)
        from ORDERS o2
        where o2.OrderType = o.OrderType
    ) / totalCount as FractionOfOrdersOfThisType
from ORDERS o

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

You can do something like

SELECT COUNT(*), 
       dt,
       round( SUM( CASE WHEN order_type = 'Utensils'
                        THEN 1
                        ELSE 0
                    END) * 100 / COUNT(*),2) fraction_of_utensils_orders
  FROM ORDERS 
 GROUP BY dt
 ORDER BY st

If you find it easier to follow, you could also

SELECT COUNT(*), 
       dt,
       round( COUNT( CASE WHEN order_type = 'Utensils'
                          THEN 1
                          ELSE NULL
                      END) * 100/ COUNT(*), 2) fraction_of_utensils_orders
  FROM ORDERS 
 GROUP BY dt
 ORDER BY st

Upvotes: 1

Related Questions