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
Concat(Column) Over(Partition By...)? Group-Concatentating Rows Without Grouping the Result Itself
Sql Max Function Returns One Row When Multiple Rows Have the Same Value
Sql - How to Sum/Aggregate Certain Rows in a Table
Retrieving the Last Record in Each Group - MySQL
How to Calculate Date Difference in Postgresql
Can You Delete Data from Influxdb
Multiple Insert With Same Column (Ms Access)
How to See Query History in SQL Server Management Studio
How to Make an If Statement in Ssis
Removing Leading Zeroes from a Field in a SQL Statement
Selecting Only One Duplicate Record
How to Get Only One Row Against Each Id in MySQL
What Is the Best Datatype for Storing Urls in a MySQL Database
How to Enforce Case Sensitive Table and Column Names in MySQL