0

I'm new here. First post. I'd really appreciate some help.

I'm trying to calculate total sales for all products sold. I have a Quantity column and a Price column. I understand how to multiply these two columns, BUT I do not know how to add them all together in the same query.

Here is an example: Quantity: 2, 3, 1 Price: 2, 4, 5 I could do Quantity * Price to get: 4, 12, 5 But then how would I add 4 + 12 + 5 to get the total? I need this step to be included in the same query.

EDIT: Both the Quantity and Price columns are in the same table. SALES (Quantity, Price)

I am using Microsoft SQL-Server.

Hyperlite147
  • 25
  • 1
  • 7
  • 3
    could you please specify what SQL engine are you using. eg SQL-Server, or MySQL etc.. Also if you could describe each table by providing the table definitions. – Fuzzy Feb 22 '16 at 19:07
  • Welcome to SO... it would help to understand your question if we can see some sample data. Also make sure to review this page (http://stackoverflow.com/help/how-to-ask). –  Feb 22 '16 at 19:08
  • Could you please provide some sample data as was done [in this solution](http://stackoverflow.com/questions/35442173/how-to-display-products-under-category-in-sql-in-a-table?answertab=votes#tab-top) (this is merely ment to be an example). – Ralph Feb 22 '16 at 19:09

1 Answers1

1

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
GabrielVa
  • 2,353
  • 9
  • 37
  • 59
  • It's just one table. That code makes sense for just multiplying Quantity * Price, but I also need to take those answers and then SUM (add) them all together. I'm new to SQL, so not sure what dbo. means. – Hyperlite147 Feb 22 '16 at 19:25
  • Sure, are you selecting an order id for example? Meaning you might have several records for the orderid and you want to SUM them all up (multiple rows)? dbo is simply a DataBase Object, in this case a table is a dbo. You table name can be whatever but sql knows it as a dbo, or schema. If the answer is right, please mark is as such by checking it, thanks. – GabrielVa Feb 22 '16 at 19:29
  • I think I've got it figured out now. This is the code I used: SELECT SUM (Quantity * Price ) AS TotalSales FROM SALES; The table does have an order id, but I didn't think it was relevant for my problem. Does this code above look correct? – Hyperlite147 Feb 22 '16 at 19:32
  • Yes that's the same as what my last snippet is, it should work in its basic form. – GabrielVa Feb 22 '16 at 19:35
  • I'm new to SQL and just doing basic stuff. I understand your more experienced code for the most part. Thank you for helping me figure it out! – Hyperlite147 Feb 22 '16 at 19:37