Multiply two columns and update its value
You're calculating the @Amt
prior to the adjusted quantity
Just change your update
to this
Update Balance
set CQty = CQty - @Qty
,CAmt = (CQty - @Qty) * Rate
WHERE ID = @Id
That said, Jarlh's comment of having a computed column is 100% correct. I would just have it persisted
.
Multiply two columns sql
I would put the subquery in the from
clause and no group by
is needed:
SELECT m.city, (f.factor1 * m.mark * 1.0) as new_mark
FROM marks m CROSS JOIN
(SELECT factor1 FROM factors) f;
How to multiply two columns and add a new column to the database?
You cannot use an alias created in a select clause in the same select clause, because the expressions have no order. This means, that product_quantity * price_per_item as "sales"
is not necessarily executed before SUM(sales)
, so the DBMS tells you that sales
is unknown.
You don't need the alias anyway, because with one result row per customer, what amount other than the sum would you want to show?
SELECT
customerid,
SUM(product_quantity * price_per_item) AS sales
FROM questions
GROUP BY customerid
ORDER BY customerid;
This doesn't get you the top customer, though. But your query didn't either :-) This is just an explanation what's wrong in your original query.
Here are two options on how to build up on this to get the top customer(s):
Option 1 with a subquery (a CTE here):
WITH list AS
(
SELECT
customerid,
SUM(product_quantity * price_per_item) AS sales
FROM questions
GROUP BY customerid
)
SELECT *
FROM list
WHERE sales = (SELECT MAX(sales) FROM list);
Option 2 with an analytic function:
SELECT customerid, sales
FROM
(
SELECT
customerid,
SUM(product_quantity * price_per_item) AS sales,
MAX(SUM(product_quantity * price_per_item)) OVER() AS max_sales
FROM questions
GROUP BY customerid
) with_max
WHERE sales = max_sales;
MS SQLServer How to multiply two columns, then add them all together..?
Example if you have one table:
SELECT dbo.orderid,
SUM(dbo.quantity * dbo.price) AS grand_total,
FROM ORDERITEM
If you have two tables instead of one, then:
SELECT oi.orderid,
SUM(oi.quantity * p.price) AS grand_total,
FROM ORDERITEM oi
JOIN PRODUCT p ON p.id = oi.productid
WHERE oi.orderid = @OrderId
GROUP BY oi.orderid
Adding all the rows (orderid numbers) up together to get a total, you would simply groupby and them select the sum value. Example below:
SELECT Orderid, SUM(quanity) AS Expr1, SUM(price) AS Expr2, SUM(quanity * price) AS Total
FROM dbo.mytable
GROUP BY pid
HAVING (pid = 2)
Or this in a SQL view showing the total QTY and Price:
SELECT Orderid, SUM(quanity) AS Quanity, SUM(price) AS Price, SUM(quanity * price) AS Total
FROM dbo.mytable
GROUP BY pid
MySQL - Use GROUP BY and SUM to multiply two columns
As noted by patrick3853 and Olivier Depriester in the comments, the line SUM(jo.quantity * jo.price) should instead be SUM(jo.quantity) * SUM(jo.price).
The correct query is as follows:
SELECT p.name AS 'Project Name',
SUM(jo.quantity) AS 'Job Order Quantity',
SUM(jo.price) AS 'Job Order Price',
SUM(jo.quantity) * SUM(jo.price) AS 'Cost'
FROM projects p
JOIN job_orders jo ON p.id = jo.project_id
GROUP BY name;
Related Topics
Splitting SQL Column into Multiple Columns Based on Value
Adding $ Dollar Sign on My Total Cost in SQL Server
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Finding the Highest Average Salary in SQL
The Network Adapter Could Not Establish the Connection in SQL Developer
Mysql Truncated Incorrect Double Value
Joining Tables from Different Servers
Ssis Failed Validation and Returned Validation Status "Vs_Isbroken"
How to Combine Two Completely Different SQL Queries into One Result
How to Remove Line Feed Characters When Selecting Data from SQL Server
Sql to Generate a List of Numbers from 1 to 100
Using Where Clause With Two Type of Conditions on Same Column
How to Combine First Name, Middle Name and Last Name in SQL Server
Find All Parent Records Where All Child Records Have a Given Value (But Not Just Some Child Records)