CODEWITHSUNDEEP
mysqlsqlaggregate
user12490809
user12490809

Reputation: 89

Row wise Operation in SQL

I have to table like

Id Stage
1 Contacted
2 Won
3 Negotiation
4 Won
5 Contacted
6 Contacted
8 Negotiation
9 Negotiation
10 Contacted

I need to get the output as

Stage Count Ratio (%)
Contacted 4 100
Negotiation 3 75
Won 2 66.67

The order of stages Contacted, Negotiation, and Won.

And is it possible to get the Conversion ratio from stage to stage by setting the order?

Thanks in Advance

Upvotes: 0

Views: 230

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You want aggregation with lag(). The trick is the ordering. For customer ordering in MySQL, field() is handy:

select state, count(*),
       (count(*) * 100 /
        lag(count(*), 1, count(*)) over (order by field(state, 'Contacted', 'Negotiation', 'Won'))
       )
from t
group by state;

Here is a db<>fiddle.

Note: Your stages happen to be in alphabetical order, so you can just use:

select state, count(*),
       (count(*) * 100 /
        lag(count(*), 1, count(*)) over (order by state)
       )
from t
group by state;

However, assuming an alphabetical ordering is not very generalizable.

Upvotes: 1

Related Questions