SQL interview question | Write the query
Question
We have two columns(Revenue and Cost Price) in a
table like below, get me the Profit column
Revenue Cost Price Profit
100 Null 100
200 20 180
300 50 250
Null 50 -50
solved
16
SQL
55 years
46 Answers
5155 views
Grand Master 6
Answers ( 46 )
select (Revenue – Cost Price) as Profit from table
Select t.Revenue – COALESCE (t.’Cost Price’,0) Profit from table t
Is ‘ ‘ required for a column name that has space in between.
Can’t we just write COALESCE(Cost Price, 0)?
SELECT ISNULL(Revenue,0)-ISNULL(CostPrice,0) As [Profit] FROM table_name
Sorry it a private answer.
Awesome 🙂
Select revenue, costprice, (revenue – costprice) as profit from table_name
Select [Revenue],
[Cost Price],
(Coalesce ( [Revenue],0) –
Coalesce ( [Cost Price],0) ) As
[Profit]
From Table
Select revenue, costprice, (revenue – costprice) as profit from table
SELECT *,
CASE WHEN revenue is NULL and cost is NULL Then 0
WHEN revenue is not NULL and cost is NULL THEN Revenue
WHEN revenue is NULL and cost is not NULL THEN -1 * cost
ELSE revenue – cost
FROM Table
SELECT (Revenue – Cost Price)
AS Profit
FROM table ;
SELECT (Revenue – Cost Price) AS Profit from TABLE
SELECT revenue,cost,(revenue-COALESCE(cost,0))profit
Select o.Revenue – COALESCE (o.’Cost Price’,0) Profit from table o
Select revenue – Coalesce (cost price, 0) as profit from table_name
We can achieve the required result using NVL function OR Case statement:
1. SELECT Revenue, Cost_Price, NVL(Revenue,0) – NVL(Cost_Price,0) as Profit
FROM Table_Name;
2. SELECT Revenue, Cost_Price,
(CASE WHEN Revenue IS NULL and Cost_Price IS NULL Then 0
WHEN Revenue IS NOT NULL and Cost_Price IS NULL THEN Revenue
WHEN Revenue is NULL and Cost_Price IS NOT NULL THEN Cost_Price*-1
ELSE Revenue – Cost_Price) as Profit
FROM Table_Name
Select table.Revenue, table.Cost_Price, coalesce(table.Revenue,0) – coalesce(table.Cost_Price,0) as Profit
select revenue,costPrice,coalesce(revenue,0)-coalesce(costPrice,0) as profit from table
Select revenue – Coalesce (cost price, 0) as profit from table_name
SELECT
Revenue
,”Cost Price”
,COALESCE(Revenue,0) – COALESCE(“Cost Price”,0) AS Profit
FROM
table
select (coalesce(Revenue, 0) – coalesce(`Cost Price`, 0)) as Profit from table;
Select (Coalesce(revenue,0) – Coalesce (cost price, 0))as profit
from Table;
this query is not working in postgresql
SELECT
Revenue, ”Cost Price”,
COALESCE(Revenue,0) – COALESCE(“Cost Price”,0) AS Profit
FROM
table_name;
select (nvl(revenue,0)-nvl(‘cost price’,0)) as profit from table
Whatever NULL touches in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is, how can you display another value instead?
— Using COALESCE
Query:
Let’s give a name to the table – Expenditure
SELECT Revenue, Cost Price, (Revenue – COALESCE(Cost Price, 0)) as Profit
FROM Expenditure;
select coalesce(Revenue,0) – coalesce( Cost Price, 0) as profit
from table_name
Select revenue – COALESCE (cost price, 0) as profit from table_name
Query:
SELECT Revenue,’Cost Price’,(COALESCE(Revenue,0)-COALESCE(‘Cost Price’,0)) as Profit FROM table;
Select Revenue,[Cost price], Revenue-[Cost price] as “Profit” from table
select revenue, “cost price”, COALESCE(revenue, 0) – COALESCE(“cost price”, 0) as profit from tablename;
SELECT
[Revenue], [Cost Price],
COALESCE(Revenue,0) -COALESCE( Cost Price,0)
AS
[Profit]
from table
SELECT (COALESCE(Revenue,0) – COALESCE(Cost Price, 0)) As Profit
FROM Table_Name
SELECT
[Revenue], [Cost Price],
COALESCE(Revenue,0) -COALESCE( Cost Price,0)
AS
[Profit]
from table
SELECT Revenue,”Cost Price”,(Revenue-“Cost Price”) as Profit FROM table
Select Revenue, Cost_Price, Coalesce(Revenue,0) – Coalesce(Cost_Price) as Profit
From Table_Name
SELECT [Revenue],
[Cost Price],
(COALESCE ( [Revenue],0) –
COALESCE ( [Cost Price],0) ) As
[Profit]
FROM Table
select (ifnull(revenue,0) – ifnull(cost,0)) as Profit from table
select (Revenue – Cost Price) as Profit From table;
select ifnull(Revenue, 0)-ifnull(Cost Price,0) as Profit from table table_name;
select revenue,cost_price,(revenue-cost_price) as profit from table.
SELECT Revenue, Cost Price, COALESCE(Revenue,0) – COALESCE(Cost Price, 0) Profit
FROM table;
select revenue, ‘cost price’, (coalesce(revenue,0)-coalesce(‘cost price’,0) as Profit from table
SELECT Revenue, Cost Price,
(Coalesce(Revenue,0) – Coalesce(Cost Price,0)) as Profit
FROM Table
select (ifnull(revenue,0) – ifnull(cost_price ,0)) as Profit from table_name;
select * ,( ifnull(revenue ,0) -ifnull(cost_price,0)) as Profit from table_name;