CODEWITHSUNDEEP
sqlcount
P. Camilleri
P. Camilleri

Reputation: 13218

Find the proportion of rows verifying a condition in a single SQL query

Suppose I have a sales table which is as follows:

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

I would like to find, in a single query, the proportion of rows verifying a given condition. For example, if the condition is Price < 1, the result should be 3/5 = 0.6.

The only workaround that I have found so far is :

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 ?

Upvotes: 4

Views: 192

Answers (1)

Alma Do
Alma Do

Reputation: 37365

You can do it with IF:

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

-but it's no big difference from CASE (your logic here is correct)

You may want to use WHERE (to sum only Price<1) - but since you're using total COUNT it's not valid in your case. Another option: get total count separately:

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

Upvotes: 2

Related Questions