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.
CodePudding user response：
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
CodePudding user response：
Assuming the first table is t1 & the second is t2:
SELECT ROUND(SUM(t1.Price * t2.Quantity), 2) AS TotalSum
FROM t1, t2
WHERE t1.ItemCode = t2.ItemCode;
