Join Two Tables and calculate SUM of Column in SQL Server

Last Reply 2 months ago By pandeyism

Posted 2 months ago

Hi,

I want yo generate a chart using data from two tables. I developed the code in Visual Studio, C# and I took the code for chart from this site.

I had two tables: Invoices and Costs.

Invoices contains: category,due_date, sum.

Costs contains: category, date, sum.

I select a month from Costs and then I want to display on the chart the total sum of the tables and all the categories once time.

How can I write the SQL query?

I share with you my current query on Costs table.

 string query = string.Format("select distinct Categorie,sum(C_Suma) from Cheltuieli where  datename(month,C_Data) = '{0}' group by Categorie", ddlLuna.SelectedValue);

The code is in Romanian, I translate above the terms.

Thank you!

Posted 2 months ago
Hi @ada20,
Please try the following

Join Two Tables and filter records in SQL Server

It might help you.

Cheers Andrea.

Posted 2 months ago

Hi ada20...

select distinct Categorie,sum(C_Suma) from Cheltuieli

There is no table called Cheltuieli.

You have mentioned only 2 tables ie.Invoices and Costs


Posted 2 months ago

Cheltuieli means Costs. 


Posted 2 months ago

Hi ada20,

Refer below sample query.

SQL

DECLARE @Invoices AS TABLE(category VARCHAR(MAX),due_date DATETIME, [sum] INT)
INSERT INTO @Invoices VALUES('Stationary accessories','2019/08/16',1250)
INSERT INTO @Invoices VALUES('Grocery ingredients','2019/08/17',5000)
INSERT INTO @Invoices VALUES('Electronic accessories','2019/08/18',2000)
INSERT INTO @Invoices VALUES('beautician accessories','2019/08/19',2000)
DECLARE @Costs AS TABLE(category VARCHAR(MAX),[date] DATETIME, [sum] INT)
INSERT INTO @Costs VALUES('Stationary accessories','2019/08/16',1250)
INSERT INTO @Costs VALUES('Grocery ingredients','2019/08/17',2000)
INSERT INTO @Costs VALUES('Electronic accessories','2019/08/18',2000)
INSERT INTO @Costs VALUES('beautician accessories','2019/08/19',2000)

SELECT i.category, SUM(i.[sum]) + SUM(c.[sum]) Suma, c.[date]
FROM @Invoices i
INNER JOIN @Costs c ON c.category = i.category
WHERE DATENAME(MONTH,c.[date])='August'
GROUP BY i.category,c.[date]

Output

category Suma date
beautician accessories 4000 2019-08-19 00:00:00.000
Electronic accessories 4000 2019-08-18 00:00:00.000
Grocery ingredients 7000 2019-08-17 00:00:00.000
Stationary accessories 2500 2019-08-16 00:00:00.000