CODEWITHSUNDEEP
sqlsumteradatadivide
VanPS
VanPS

Reputation: 13

Dividing sum results

I'm really sorry as this was probably answered before, but I couldn't find something that solved the problem.

In this case, I'm trying to get the result of dividing two sums in the same column.

| Id | month | budget | sales |
| -- | ----- | ------ | ----- |
| 1  | jan   | 1000   | 800   |
| 2  | jan   | 1000   | 850   |
| 1  | feb   | 1200   | 800   |
| 2  | feb   | 1100   | 850   |

What i want is to get the % of completition for each id and month (example: get 0,8 or 80% in a fifth column for id 1 in jan) I have something like

sel 
id,
month,
sum (daily_budget) as budget,
sum (daily_sales) as sales,
budget/sales over (partition by 1,2) as efectivenes
from sales
group by 1,2

I know im doing this wrong but I'm kinda new with sql and cant find the way :| Thanks!

Upvotes: 1

Views: 1105

Answers (3)

hfontanez
hfontanez

Reputation: 6178

If you want to ALTER your table so that it contains a fifth column where the result of budget/sales is automatically calculated, all you need to do this add the formula to this auto-generated column. The example I am about to show is based on MySQL.

  1. Open MySQL
  2. Find the table you wish to modify in the Navigator Pane, right-click on it and select "Alter Table"

enter image description here

  1. Add a new row to your table. Make sure you select NN (Not Null) and G (Generated Column) check boxes
  2. In the Default/Expression column, simply enter the expression budget / sales.

enter image description here

Once you run your next query, you should see your column generated and populated with the calculated results. If you simply want the SQL statement to do the same from the console, it will be something like this: ALTER table YOUR_TABLE_NAME add result FLOAT as (budget / sales);

Upvotes: 0

d0little
d0little

Reputation: 476

This should do it

CAST(ROUND(SUM(daily_sales) * 100.00 / SUM(daily_budget), 1) AS DECIMAL(5,2)) AS Effectiveness

Upvotes: 2

KP6
KP6

Reputation: 1

I'm new at SQL too but maybe I can help. Try this?

sel 
id,
month,
sum (daily_budget) as budget,
sum (daily_sales) as sales,
(sum(daily_budget)/sum(daily_sales)) over (partition by id) as efectivenes
from sales
group by id

Upvotes: 0

Related Questions