Sql Divide 2 Columns

How to divide two columns?

Presumably, those columns are integer columns - which will be the reason as the result of the calculation will be of the same type.

e.g. if you do this:

SELECT 1 / 2

you will get 0, which is obviously not the real answer.
So, convert the values to e.g. decimal and do the calculation based on that datatype instead.

e.g.

SELECT CAST(1 AS DECIMAL) / 2

gives 0.500000

SQL divide 2 columns

Use nullif():

select name, population, gdp, area,
population / nullif(area, 0) as pop_density
from country
order by gdp;

The group by doesn't seem necessary.

nullif() is a standard function that returns NULL when the two values passed in are equal. If your database does not support it, you can do the same thing with a case expression.

SQL dividing two columns not working properly

Try this way please:

select purchase_price, pages, (cast(purchase_price as decimal) / pages) as pageprice
from books
where purchase_price >= pages
order by pageprice desc

SQL query how to divide values from two columns from the same table in different condition

try like below

select code, 
sum(case when remark=111 then val else 0 end)/sum(case when remark=222 then val else 0 end) as val
from table where code= '00001' and date= '20191107'
group by code

Divide the count of two columns in SQL

Be careful here doing division. When you have count / count you have integer math. So something like 3/2 will result in 1, not 1.5. I modified your sample data a little bit to demonstrate what I mean. I included both calculations in the output so you can the difference.

declare @Something table
(
A int
, B int
, C varchar(20)
)
insert @Something values
(30, 10, 'tomatoes')
, (30, 11, 'tomatoes')
, (5 , 10, 'tomatoes')
, (20, 5 , 'Potatoes')
, (20, 5 , 'Potatoes')
, (40, 5 , 'Potatoes')
, (10, 15, 'Onions')
, (40, 15, 'Onions')
, (20, 15, 'Onions')

select count(A)
, count(distinct B)
, count(A) / (count(distinct B) * 1.0) --multiplied by 1.0 to force division to use a decimal
, count(A) / count(distinct B) --integer math
, C
from @Something s
group by C

How to divide two columns in same table and append the result to the end of the table in SQL

Add computed columns. For instance:

alter table table1
add PHAvgD as (HPProD / nullif(HPSalD));

You can repeat this for each new column.

The advantage of this approach is that the values are calculated when they are fetched -- so the calculations are always accurate for new rows and updated column values.

divide the value of a column by another column

Select total_percent / no_of_scren as 'result' From yourTableName

Divide two columns into group by function SQL

You cannot access the alias in the SELECT clause. So you have to repeat sum(value1):

select col1, col2, col3, 
sum(value1) as col4,
col5,
sum(value1) / col5 as col6
from table1
group by col1, col2, col3, col5

Divide the column by the sum grouped by another column

You can use SUM() window function.

If you want a numerical result:

SELECT source, destination,
frequency / SUM(frequency) OVER(PARTITION BY source)
FROM tablename

Depending on your database, if it performs integer division between integers, you may need to multiply by 1.0:

SELECT source, destination,
1.0 * frequency / SUM(frequency) OVER(PARTITION BY source)
FROM tablename

If you want the result as a string you can do it with concatenation:

SELECT source, destination,
CONCAT(frequency, '/', SUM(frequency) OVER(PARTITION BY source))
FROM tablename

I used CONCAT(), but if your database does not support it use its concatenation operator and also you may need to convert the integer values to strings.



Related Topics



Leave a reply



Submit