CODEWITHSUNDEEP
mysqlview
Raj
Raj

Reputation: 23

How can the below 3 select statements be included in a single mySQL view

Need to create a single view that executes the below 3 select statements

I am able execute them in a SQL editor and get the ratio.

select @lag := count(twitter_handles) from handle_impressions where today_impressions > yest_impressions;
select @decay := count(twitter_handles) from handle_impressions where today_impressions < yest_impressions;
select @ratio := @lag/@decay;

The intent is to have a single view encapsulating the 3 select statements to return lag, decay and ratio

Upvotes: 2

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

We can try writing a single query using conditional aggregation:

SELECT
    COUNT(CASE WHEN today_impressions > yest_impressions THEN 1 END) AS lag_val,
    COUNT(CASE WHEN today_impressions < yest_impressions THEN 1 END) AS decay_val,
    COUNT(CASE WHEN today_impressions > yest_impressions THEN 1 END) /
        COUNT(CASE WHEN today_impressions < yest_impressions THEN 1 END) AS ratio
FROM handle_impressions;

Upvotes: 2

Related Questions