Click here to Skip to main content
13,868,480 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I am a beginner developing a WinForms application in Visual Studio 2015 using C# .Net connecting to a MS Access 2016 database. What I am trying to do is to add (SUM, I guess) the values of three columns: two from the same table and one column from another. The table structure is as follows:

-------------------------------------------------

Table Products: Product, Cost1, Cost2

Table OtherCosts: Cost3

-------------------------------------------------

I need a query that returns the following:

Product, AllCosts

-------------------------------------------------

Thank you very much for your time and help. I really appreciate it.

What I have tried:

SELECT Product
FROM tbl_products

UNION SELECT Price1 AS Price
FROM tbl_products

UNION SELECT Price2 AS Price
FROM tbl_products

UNION SELECT Proce3 AS Price
FROM tbl_othercosts;
Posted
Updated 14-Sep-18 14:42pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Somehow you need to join both tables; as we do not know the structure of these tables and how they are really related, it is hard to provide a precise answer.
This could be something like:
SELECT
  a.Product
 ,a.Price1 + a.Price2 + b.Price3 as 'Total cost'
FROM
 tbl_Products a
 INNER JOIN tbl_othercosts b ON b.ForeignKey = a.PrimaryKey
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Use the Sum function with the Join:

SELECT p.Product, SUM(oi.quantity * p.price) AS grand_total,
FROM ORDERITEM oi
    JOIN PRODUCT p ON p.id = oi.productid
        WHERE oi.orderid = @OrderId
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190214.1 | Last Updated 14 Sep 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100