Multiply rows of a

Question

Let's say I have a database table that looks like this:

ItemCode	Price
ABC	12.50
DEF	9.99

Then let's say I have a separate GUI where I am trying to run a query that finds the sum of all rows on my dynamic GUI against it's quantity*price from the database.

ItemCode	Quantity
ABC	5
DEF	8

So for the above table, the result of my select query would be 142.42. The confusion I have is that the GUI table I am making can have any Quantity inputted, so where do I feed the Quantity into the query. I thought about running a select query per line and using C# to multiply the results but that seems like a unnecessary load of queries to run each time.

i would use a view for sake of performance returning the calculation. — jmvcollaborator, Sep 10 '22 at 00:38
By "GUI table" do you mean you're using some kind of `DataGrid` or `DataGridView`? If so, why use SQL at all? — Dai, Sep 10 '22 at 00:50
So the prices are in the database and the amounts in your app? Then just select the prices from the database and do the calculation in your app. — Thorsten Kettner, Sep 10 '22 at 07:18

score 0 · Answer 1 · answered Sep 10 '22 at 01:40

0

If you are looking to pass your GUI table as a parameter, you would put your data into a DataTable in C# and create a table-valued parameter in SQL Server with the same structure. See this post for more details

answered Sep 10 '22 at 01:40

buh.dub

11
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 15 '22 at 01:35

Ahmed Ismail · Answer 2 · 2022-09-10T06:38:33.473

-3

Assuming your two tables are t1 & t2:

SELECT ROUND(SUM(t1.Price * t2.Quantity), 2) AS Sum
FROM t1
INNER JOIN t2
ON t1.ItemCode = t2.ItemCode;

Sum
142.42

fiddle

edited Sep 10 '22 at 06:38

answered Sep 10 '22 at 01:10

Ahmed Ismail

44
4

4

Explicit JOIN is the way to go from now on. Take a peek at https://blog.sqlauthority.com/2015/10/08/sql-server-why-should-you-not-to-use-old-style-join/ – John Cappelletti Sep 10 '22 at 01:44
1

Thanks for the info. I only did the old way because I thought it was faster but I found out that they both are the same or close performance wise so the explicit join would be much better for readability and catching errors. – Ahmed Ismail Sep 10 '22 at 06:35

Multiply rows of a

2 Answers2