Multiplying Two Columns in SQL Server

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