Sum of three table columns in SQL?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i have problem to sum of three table

What I have tried:

i have did on table section sum..but do not have idea how to sum remain two table...
here is my data and query ]

CREATE TABLE #Catagory (CID INT,CName VARCHAR(50))
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT)
CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,QTY INT,Entrydate DATETIME)
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)
CREATE TABLE #Employee(EID INT,SecID INT,QTY int, Entrydate DATETIME)
 
INSERT INTO #Catagory VALUES(1,'INDIA')
INSERT INTO #Catagory VALUES(2,'INDIA(Cut)')
INSERT INTO #Catagory VALUES(3,'Pakistan')
INSERT INTO #Catagory VALUES(4,'Pakistan(Cut)')
 
 
INSERT INTO #Sections VALUES(1,'HR')
INSERT INTO #Sections VALUES(2,'Baby')
INSERT INTO #Sections VALUES(3,'Ladies')
INSERT INTO #Sections VALUES(4,'Mix Rammage')
 
INSERT INTO #ItemMasterFile VALUES(1,'A',1,1)
INSERT INTO #ItemMasterFile VALUES(2,'B',2,2)
INSERT INTO #ItemMasterFile VALUES(3,'C',3,3)
INSERT INTO #ItemMasterFile VALUES(4,'D',4,null)
INSERT INTO #ItemMasterFile VALUES(5,'e',5,null)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,null)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2)
INSERT INTO #ItemMasterFile VALUES(10,'L',4,3)
INSERT INTO #ItemMasterFile VALUES(11,'M',2,4)
 
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019')
INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02-06-2019')
INSERT INTO #Bigbalprd VALUES(3,4,null,4,1,'03-06-2019')
INSERT INTO #Bigbalprd VALUES(4,4,null,4,1,'04-06-2019')
INSERT INTO #Bigbalprd VALUES(4,5,null,4,1,'04-06-2019')

INSERT INTO #Probale VALUES(1,1,1,'01-06-2019')
INSERT INTO #Probale VALUES(2,3,1,'02-06-2019')
INSERT INTO #Probale VALUES(3,11,1,'03-06-2019')
INSERT INTO #Probale VALUES(4,10,1,'08-06-2019')
INSERT INTO #Probale VALUES(3,8,1,'03-06-2019')
INSERT INTO #Probale VALUES(4,9,1,'08-06-2019')
INSERT INTO #Probale VALUES(4,9,1,'08-06-2019')

INSERT INTO #Employee VALUES(1,1,4,'01-05-2019')
INSERT INTO #Employee VALUES(2,3,5,'02-05-2019')
INSERT INTO #Employee VALUES(3,3,3,'03-05-2019')
INSERT INTO #Employee VALUES(4,4,7,'04-05-2019')
 
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '09-06-2019'
 
SELECT DISTINCT s.Secnam, ISNULL(SUM(b.prdQTY),0)QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Probale b ON  i.CodeItem = b.CodeItem
FULL OUTER JOIN #Sections s ON i.SecID = s.SecID

WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL
GROUP BY  s.Secnam
 
DROP TABLE #Catagory
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd
Drop Table #Employee
Drop Table #Probale

here is my output which i want
Total — imgbb.com[^]

Posted 26-Jun-19 19:41pm

akhter86

Updated 26-Jun-19 23:36pm

Add a Solution

Comments

Maciej Los 27-Jun-19 3:10am

Can you be more specific and provide more details about you issue, especially about desired result.

akhter86 27-Jun-19 3:38am

here is my desired result https://ibb.co/Jz98hwh

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2019-06-26T23:37:00

First of all, a small tip:
You can define variable of type table:

SQL

DECLARE @Catagory TABLE(CID INT,CName VARCHAR(50))

You can use simpler form of INSERT INTO statement:

SQL

INSERT INTO @Catagory VALUES(1,'INDIA'), (2,'INDIA(Cut)'), (3,'Pakistan'), (4,'Pakistan(Cut)')

Now, a solution...
Try this:

SQL

SELECT s.Secnam, ISNULL(SUM(b.QTY),0) BigbalQTY, ISNULL(SUM(p.prdQTY),0) ProbableQTY, ISNULL(SUM(b.QTY),0) + ISNULL(SUM(p.prdQTY),0) Total,
		(SELECT ISNULL(SUM(DISTINCT QTY),0) FROM @Employee WHERE SecID = s.SecID) EmpQTY
FROM @ItemMasterFile i
	LEFT JOIN @Probale p ON  i.CodeItem = p.CodeItem
	LEFT JOIN @Sections s ON i.SecID = s.SecID
	LEFT JOIN @Bigbalprd b  ON  i.CodeItem = b.CodeItem
WHERE (p.Entrydate BETWEEN @StartDate AND @Enddate) OR p.Entrydate IS NULL
GROUP BY  s.Secnam, s.SecID

To get total, you have to "add" new row, which you are able to achieve by using UNION ALL[^] statement:

SQL

UNION ALL
SELECT 'Total', SUM(T.BigbalQTY), SUM(T.ProbableQTY), SUM(T.Total), SUM(T.EmpQTY)
FROM (
   --above query here
) AS T

I'd suggest to drop result of first query into[^] temporary table:

SQL

SELECT ...
INTO #tmpTable
FROM ...

Then you'll be able to simplify your query statement into this form:

SQL

SELECT a.*
FROM #tmpTable a
UNION ALL
SELECT 'Total', SUM(...)
FROM #tmpTable