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

Question

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)

You should read https://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql — P.Salmon, Sep 01 '19 at 11:59

score 1 · Accepted Answer · answered Sep 01 '19 at 11:04

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;

unfortunately using mysql version 5.6 – jack miao Sep 01 '19 at 11:08 — jack miao, Sep 01 '19 at 11:08

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

1 Answers1