1

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      |
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.