Query to calculate percentage of boolean value field

Question

I have a data extension which has a boolean field option. I have another data extension which has 2 fields option and percent. I'd like to write a query that gives me a percent value of the number of true and false values in the original table. The column types in the destination table don't really matter and could be text, I just need to get the values.

The output would look like this:

| option | percent |
|--------|---------|
| true   | 35      |
| false  | 65      |

Answer 1

There may be a better, more efficient way to do this, but this'll work in SFMC.

Schema

CREATE TABLE widgets
  (
     id int primary key, 
     [option] bit
    );

INSERT INTO widgets
(id, [option])
VALUES
(1,1),
(2,0),
(3,0),
(4,1),
(5,1),
(6,0),
(7,1),
(8,0),
(9,0),
(10,0)
;

Query

select distinct
w.[option]
, cast(w1.widgetCount as decimal(18,4)) / cast(w2.totalCount as decimal(18,4)) * 100 as [percent]
from widgets w
outer apply (
  select 
  count(*) widgetCount
  from widgets w1a
  where w1a.[option] = w.[option]
) w1
outer apply (
  select 
  count(*) totalCount
  from widgets w2a
) w2

Output

| option | percent |  
|--------|---------|
| false  |      60 |
| true   |      40 |

Source: SQLFiddle