CODEWITHSUNDEEP
mysqlsql
jack miao
jack miao

Reputation: 1488

mysql query to get percentage of certain field in a table?

I have table of expenses:

Expense(amount: Double, vendorId: Int, vendorType: Int)

I want to create a mysql query that will give me the percentage of each vendor type with certain vendor, like:

vendorId   vendorType percentOfExpencesOfThisType    totalExpenses

so lets say I have 4 expences:

Expense(amount: 30.0, vendorId: 3, vendorType: 1)
Expense(amount: 58.5, vendorId: 3, vendorType: 1)
Expense(amount: 47.0, vendorId: 3, vendorType: 7)
Expense(amount: 21.5, vendorId: 3, vendorType: 13)

so the table will look:

vendorId   vendorType percentOfExpencesOfThisType    totalExpenses
3               1                 50                       4
3               7                 25                       4
3               13                25                       4

how would I do that? (unfortunately using mysql version 5.6)

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use aggregation and window functions:

select vendorId, vendorType,
       100 * count(*) / sum(count(*)) over (partition by vendorId) as percentOfExpensesOfThisType,
       sum(count(*)) over (partition by vendorId) as totalExpenses
from expense
group by vendorId, vendorType;

Window functions are available starting in MySQL 8+.

In earlier versions, you would be joining two aggregation queries:

select vendorId, vendorType,
       100 * vt.cnt / v.cnt as percentOfExpensesOfThisType,
       v.cnt as totalExpenses
from (select vendorId, vendorType, count(*) as cnt
      from expense
      group by vendorId, vendorType
     ) vt join
     (select vendorId, count(*) as cnt
      from expense
      group by vendorId
     ) v
     on vt.vendorId = v.vendorId;

Upvotes: 1

Related Questions