Click here to Skip to main content
13,733,504 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 14-Sep-18 13:03pm
Updated 14-Sep-18 13: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
  Permalink  
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
  Permalink  
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-2016 | 2.8.180920.1 | Last Updated 14 Sep 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

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