Find the fraction of rows checking a condition in a single SQL query

Question

Suppose I have a table salesthat looks like this:

ID    |      Price  
----------------------
1              0.33                
2              1.5               
3              0.5                
4              10                
5              0.99    

I would like to find in one query a fraction of the lines checking this condition. For example, if the condition is Price < 1, the result should be 3/5 = 0.6.

The only workaround I have found so far:

SELECT
    SUM(
        CASE
            WHEN Price < 1
            THEN 1 
            WHEN Price >= 1
            THEN 0
            END
          )/COUNT(*)
FROM sales

but is there a way to do this without CASE?

Answer 1

You can do this with IF:

SELECT SUM(IF(Price < 1, 1, 0))/COUNT(*) FROM sales

- but that doesn't really matter from CASE(your logic is correct here)

WHERE ( Price < 1) - COUNT, . : :

SELECT 
  COUNT(sales.Price)/total_count
FROM
  sales
    CROSS JOIN (SELECT COUNt(*) AS total_count FROM sales) AS c
WHERE
-- you're summing 1 or 0 depending of Price, so your sum is 
-- just count where Price<1
  sales.Price<1