Select where 2 columns selected are from 1 column of table but with different conditions

Question

Am going in circles on a query and would appreciate your help as I'm very new at this. I'm using postgre sql, version: 9.5.8

What I'm trying to do:

I want to work out the percentage of partial sales over full sales.

The part I'm completely messing up is the final select, where I am selecting the same column COUNT(sale_id) twice from the table "sale", but passing COUNT(sale_id) through 2 different conditions (one with a 'WHERE' and one without) to create 2 new columns. Do I need to join these as separate tables instead?

The desired result should be a percentage.

This is what I have (but I'm of course getting a bunch of errors):

SELECT ROUND(percentage_partial_sale, 1) as "percentage_partial"
FROM (
SELECT count_partial_sale / count_all_sales as percentage_partial_sale
FROM (
SELECT COUNT(sale_id) FROM sale WHERE sale.is_partial=true as "count_partial_sale", 
COUNT(sale_id) FROM sale as "total_sellouts");

If you could express the solution in layman's terms that would be helpful. Feel free to make changes as you wish.

Many thanks for your help.

Answer 1

Use CASE WHEN to count conditionally:

select
  count(*) as all_sales,
  count(case when is_partial then 1 end) as partial_sales,
  count(case when is_partial then 1 end)::decimal / count(*)::decimal * 100.0 as ratio
from sale;

Answer 2

You can simply compute the average value of is_partial casted to integer (false is 0, true is 1):

[local] #= CREATE TABLE sale (is_partial boolean);
CREATE TABLE

[local] #= INSERT INTO sale VALUES (false), (false), (true);
INSERT 0 3

[local] #= SELECT AVG(is_partial::int) FROM sale;
┌────────────────────────┐
│          avg           │
├────────────────────────┤
│ 0.33333333333333333333 │
└────────────────────────┘
(1 row)

Time: 6,012 ms

---

If your use case can't be done using AVG, you can use FILTER to remove rows from an aggregate function:

[local] #= SELECT COUNT(*) FILTER (WHERE is_partial) / COUNT(*) :: float
           FROM sale;
┌───────────────────┐
│     ?column?      │
├───────────────────┤
│ 0.333333333333333 │
└───────────────────┘
(1 row)