CODEWITHSUNDEEP
sqlsql-server
Abdigani Aden
Abdigani Aden

Reputation: 11

How do I get percentage amount of categorical variables per day using SQL?

I've been stuck at this but my end goal is to get the % of negative, %positive, and % neutral for the overall data and group by dates (daily) as well as the categories. Thank you.

enter image description here

Upvotes: 1

Views: 1066

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Just use window functions:

select mlsentimentzone,
       (count(*) * 1.0 / sum(count(*)) over ()) as ratio
from t
group by mlsentimentzone;

Or, if you want this by date, use conditional aggregation:

select date,
       avg(case when mlsentimentzone = 'negative' then 1.0 else 0.0 end) as negative,
       avg(case when mlsentimentzone = 'neutral' then 1.0 else 0.0 end) as neutral,
       avg(case when mlsentimentzone = 'positive' then 1.0 else 0.0 end) as positive
from t
group by date
order by date;

Upvotes: 4

Related Questions