Calculating Percentage (%) of Total Sum in SQL
How to compute the ratio of a particular row to the sum of values?
This question comes up frequently when you want to the relative contribution of a row against the backdrop of the total sum of all the rows. For example:
- how is the browser marketshare changing over time
- what's each sales person's contribution to your company's revenue
Consider a table with the number of page view (in billions) with each browser:
| Browser | Pageviews |
|---|---|
| Chrome | 7.1685 |
| Safari | 1.935 |
| Firefox | 1.3455 |
| UC Browser | 1.0965 |
| IE | 1.341 |
| Opera | 0.816 |
| Android | 0.7245 |
| Rest | 1.2 |
What we really want to see is the browser market share. We can use a Common Table Expression (CTE) to compute the total pageview which we then use to compute the ratio:
Which gives a ratio of each browser to the total:
| Browser | Share |
|---|---|
| Chrome | 0.895 |
| Safari | 0.241875 |
| Firefox | 0.1681875 |
| UC Browser | 0.1370625 |
| IE | 0.167625 |
| Opera | 0.102 |
| Android | 0.0905625 |
| Rest | 0.15 |
And the visualization:
Percentage to Total per Group
The next question to ask is how this is changing over time?
What we are attempting to do here is to group our data into months, compute the total for that group and for each row within that group compute a ratio. An overall total wouldn't make sense. Conside the pageview table as before, but with an additional date field:
| dt | Browser | Pageviews |
|---|---|---|
| 2016-01-01 | Chrome | 7.1685 |
| 2016-01-01 | Safari | 1.935 |
| 2016-01-01 | . | . |
| 2016-01-02 | Chrome | 7.2485 |
| 2016-01-02 | Safari | 1.721 |
| 2016-01-02 | . | . |
| . | . | . |
| 2016-12-31 | Chrome | 7.864 |
| 2016-12-31 | Safari | 2.011 |
| 2016-12-31 | . | . |
We once again to resort to window functions with a partition over the month portion of the datetime.
Let's unpack the query a bit. Our window function creates partitions (groups) for each month, and for each partition we sum up the pageviews. The ratio between the current row pageviews and the total for that group is exactly what we want.
Redshift has ratio_to_report
Fortunately on Redshift, it supports a window function called ratio_to_report which computes the ratio of the value of a column in a row with that of the sum of the said expression over the group.
Как составить MySQL запрос для начисления процента?
Помогите, пожалуйста, правильно составить такой запрос: Вывести всех продавцов, у которых есть 5 пар клиентов и посчитать 10% от суммы покупок этих клиентов (для теста можно кол-во пар сократить). Вот какой код сейчас есть:
Но он выводит что-то неадекватное.
Таблицы и код можно посмотреть по ссылке: ссылка.
Помогите, пожалуйста, составить этот запрос.
UPD1:
Как я вижу решение:
Взять af_id из таблицы arur
Вычислить все r_w_uid относящиеся к этому af_id из таблицы arur
Подсчитать их количество.
Как только общее количество r_w_uid достигнет, например, 6, то суммировать значения amount_value из таблицы orders относящиеся к uid из таблицы orders при условии status = Completed.
Но я могу с лёгкостью ошибаться.
Умножить полученное в предыдущем пункте значение на 0.1 и вывести значение.
UPD2:
Связи таблиц:
orders.uid = u_af.uid
u_af.id = arur.af_id
UPD3:
Описания таблиц:
1)
orders — таблица с информацией о платежах.
orders.uid — столбец с внутренним id пользователя.
orders.amount_value — столбец с суммой платежа.
orders.status — столбец со статусом платежа.
2)
u_af — таблица нужна для связи таблицы orders с таблицей arur.
3)
arur — таблица отношений между продавцами и клиентами.
arur.af_id — столбец с id продавца.
arur.r_w_uid — столбец с id клиента.
Пары формируются следующим образом:
Если посмотреть в таблицу arur, то можно увидеть что для, например, af_id = 52 есть несколько записей в столбце r_w_uid. Если взять все записи r_w_uid относящиеся af_id = 52 и поделить их на 2, то получим количество пар. Остаток не включается в пары.
- Вопрос задан более трёх лет назад
- 361 просмотр
- Вконтакте
Вы уж извините, но то что Вы написали в запросе . это даже приблизительно не то что Вы хотели получить на словах.
Да и на словах как то все поплыло «Вывести всех клиентов, у которых есть 5 пар клиентов» — кто на ком стоял?
Если в 2 словах по запросу, у Вас 2 LEFT JOIN, оба не нужны.
У Вас нет вообще связки между двумя экземплярами orders, да и не надо там 2 раза.
Ну в целом . работает долго и непонятно, будет данных больше, будет работать днями .
P.S. после долгих уточнений подходящий ответ такой, для MySQL 8+
Для более младших версий
- Вконтакте
select
arur.af_id,
sum(o.amount_value) * 0.1 sum_
from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = ‘Completed’)
where u_af.id = arur.af_id
group by arur.af_id
having count(arur.r_w_uid) > 5
Я не очень понял по поводу status = Completed, просто вписал что считать только их
Вы, уважаемый, странные вещи говорите. т.к. Вы ищете кол-во эфимерных пар (вам просто надо делить на 2, нет зависимости в данных отвечающих за парность) то не морочте голову ни себе ни людям. Умножте на 2 вашу константу с которой сверяете (5 пар = 10 значений) и опишите это условие в having указав «>» или «>=» вашего сокрального значения «то получим количество пар. Остаток не включается в пары.»
select
arur.af_id,
sum(o.amount_value) * 0.1 sum_10p
from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = ‘Completed’)
where u_af.id = arur.af_id
group by arur.af_id
having count(arur.r_w_uid) > 5*2
- Вконтакте
как Вы сами написали o.uid = 60, а это 9*1 000 000 = 9 000 000
Разберитесь со связями в таблицах, я первые селекты писал не загружая данные.
Т.к. Вам надо работать с парами, и в этом коментарии стало понятна связь, я напишу селект ниже, но он все еще на старых условиях. Как только подгоните условия, все должно посчитаться быстро.
так, я немного запутался с парами, я написал вверху парные операции, а Вам нужны парные клиенты .
Напишите селект который выводит пары для af_id = 52 и условие сортировки (кто неудачник, станет 9-м)
И напишите связку клиентов с orders, а то я не понимаю откуда 1 550 000 для af_id = 52
Максим, Запрос на получение отношений продавцов и клиентов.
Неудачником станет тот у которого больший arur.r_w_uid. В данном запросе arur.r_w_uid = 91
Т.к. появились нормальные связи, я накидал селект
Пока оставил но совсем непонятно зачем нам таблица u_af .
оставил left join . т.е. если у продавца есть 5 пар и больше клиентов но нет операций, строка будет выведена, насколько это нужно — не знаю
4 Ways to Convert a Number to a Percentage in SQL Server (T-SQL)
Here are 4 ways to convert a number to a percentage value in SQL Server.
Strictly speaking, we’re not actually “converting” it to a percentage. We’re formatting the number as a percentage. But in order to do that, we need to convert the number from a numeric data type to a string.
Here are 4 ways to do that.
Example 1 – The FORMAT() Function
The most obvious choice to use is the FORMAT() function. This allows you to display numbers and dates in a specific format.
Here’s an example of using this function to display a number as a percentage:
Notice that four zeros were added to our value (two before the decimal point, and two after).
The following would be required to make this 55 percent:
If the number is the actual percentage value that you want, you can do this:
You can also remove the fractional part by adding a zero to the format specifier:
If required, you can also add more decimal places:
Example 2 – The CONVERT() Function
You can alternatively use the CONVERT() function to convert the number to a string, then add a percentage sign to the end.
This might seem a bit unnecessary given how easy the previous example made it, however, the FORMAT() function was only introduced in SQL Server 2012. Therefore, this is how you will need to do it if you use an earlier version of SQL Server.
Of course, if your number is something like .55 and you need that to display as 55.00 %, then you can always multiply it by 100:
In this case I also increased the size of the varchar data type to cater for the extra characters.
Also, you can remove the fractional part by using the LEFT() function:
Although you’d need to be careful when doing this, as the actual value could vary to be more or less than 2. In which case, you could use the TRIM() function to trim leading zeros and/or trailing dots:
However, this is not perfect, and the FORMAT() function obviously provides a lot more flexibility with a minimum of code.
Example 3 – The CAST() Function
We can alternatively use the CAST() function to do the same thing as the previous example:
Note that CAST() and CONVERT() use slightly different syntaxes. In the case of CAST() the value to be cast comes first, whereas it’s the other way around with CONVERT() .
Example 4 – The CONCAT() Function
You can also use the CONCAT() function to concatenate a number with the percentage sign:
This function implicitly converts all arguments to string types before concatenation.
How to calculate percentage with a SQL statement
I have a SQL Server table that contains users & their grades. For simplicity’s sake, lets just say there are 2 columns — name & grade . So a typical row would be Name: «John Doe», Grade:»A».
I’m looking for one SQL statement that will find the percentages of all possible answers. (A, B, C, etc. ) Also, is there a way to do this without defining all possible answers (open text field — users could enter ‘pass/fail’, ‘none’, etc. )