How to join and sum table (MS SQL)

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

i have two table Tbl_Device and Tbl_ReceivedQty
I want the sum of record with inner join Device

i tried to join with this query but failed

SQL

SELECT     Tbl_Device.Device_Name, Tbl_ReceivedQty.Qty_Device_ID, Tbl_ReceivedQty.Qty_User, Tbl_ReceivedQty.Qty_Date
 FROM         Tbl_Device INNER JOIN  Tbl_ReceivedQty ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
 where Qty_Date  between '2013-07-21' and '2013-07-23'  (select Qty_Device_ID, sum(Qty_Received) from Tbl_ReceivedQty group by Qty_Device_ID)

how can i join and sum table

Posted 23-Jul-13 1:01am

$ultaNn

Updated 23-Jul-13 5:32am

Joezer BH

v2

Add a Solution

3 solutions

Solution 1

Try this:
SQL

SELECT Qty_Device_ID, sum(Qty_Received) FROM Tbl_ReceivedQty WHERE Qty_Date between '2013-07-21' and '2013-07-23' GROUP BY Qty_Device_ID

SQL

SELECT T1.Device_Name, T2.Qty_Device_ID, T2.Qty_User, T2.Qty_Date, SUM(T3.Qty_Received) AS SubTotal
FROM Tbl_Device AS T1 INNER JOIN  Tbl_ReceivedQty AS T2 ON T1.Device_ID = T2.Qty_Device_ID 
    INNER JOIN Tbl_ReceivedQty AS T3 ON T1.Qty_Device_ID = T3.Qty_Device_ID
WHERE Qty_Date  BETWEEN '2013-07-21' AND '2013-07-23'
GROUP BY T1.Device_Name, T2.Qty_Device_ID, T2.Qty_User, T2.Qty_Date

Posted 23-Jul-13 1:12am

Maciej Los

Updated 23-Jul-13 1:57am

v4

Comments

$ultaNn 23-Jul-13 7:26am

also i want inner join with Tbl_Device Table

Maciej Los 23-Jul-13 7:43am

Have a look now ;)

$ultaNn 23-Jul-13 7:47am

the query works but it cannot group by Qty_Device_ID
its repeating records

Maciej Los 23-Jul-13 7:57am

See updated solution ;)

$ultaNn 23-Jul-13 8:12am

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Qty_Device_ID'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Qty_Date'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Qty_Date'.

$ultaNn 23-Jul-13 8:18am

Dear Maciej Los the query failed . if i change the date then execute still its getting sum of whole record instead of getting sum so specific date records

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)

vinayoukuri · Accepted Answer · 2013-07-23T01:17:00

Solution 2

I have just refined your query. Hope this is helpful for you.

SQL

SELECT distinct Tbl_Device.Device_Name,
temp1.Qty_Device_ID,
Tbl_ReceivedQty.Qty_User,
Tbl_ReceivedQty.Qty_Date,
temp1.sumOfQty
FROM Tbl_Device
INNER JOIN Tbl_ReceivedQty
ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
inner join (
select Qty_Device_ID as 'Qty_Device_ID', sum(Qty_Received) as 'sumOfQty' 
from Tbl_ReceivedQty where Qty_Date between '2013-07-21' and '2013-07-23'
group by Qty_Device_ID) temp1
on Tbl_Device.Device_ID = temp1.Qty_Device_ID

Posted 23-Jul-13 1:17am

vinayoukuri

Updated 23-Jul-13 5:30am

v6

Comments

$ultaNn 23-Jul-13 7:22am

i got error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'temp1'

vinayoukuri 23-Jul-13 7:28am

please check this ..

SELECT Tbl_Device.Device_Name,
Tbl_ReceivedQty.Qty_Device_ID,
Tbl_ReceivedQty.Qty_User,
Tbl_ReceivedQty.Qty_Date,
temp1.sumOfQty
FROM Tbl_Device
INNER JOIN Tbl_ReceivedQty
ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
inner join (select Qty_Device_ID, sum(Qty_Received) as 'sumOfQty' from Tbl_ReceivedQty group by Qty_Device_ID) temp1
on Tbl_Device.Device_ID = temp1.Qty_Device_ID
where Qty_Date between '2013-07-21' and '2013-07-23'

$ultaNn 23-Jul-13 7:38am

the query works but it cannot group by Qty_Device_ID
its repeating records

vinayoukuri 23-Jul-13 8:15am

I have updated the query to group by qty_device_id.

$ultaNn 23-Jul-13 8:17am

Dear Vinay the query failed . if i change the date then execute still its getting sum of whole record instead of getting sum so specific date records

vinayoukuri 23-Jul-13 8:24am

i have updated again

$ultaNn 23-Jul-13 8:32am

Cheersss.,.......!!!!!

worked fine when removed date column from select statement..

ArunRajendra · Accepted Answer · 2013-07-23T01:18:00

Solution 3

If you want all the fields try this option.

SQL

 SELECT     Tbl_Device.Device_Name, Tbl_ReceivedQty.Qty_Device_ID, Tbl_ReceivedQty.Qty_User, Tbl_ReceivedQty.Qty_Date,
 (select sum(Qty_Received) From Tbl_ReceivedQty where Qty_Device_ID = Tbl_Device.Device_ID) as SubTotal
FROM         Tbl_Device INNER JOIN  Tbl_ReceivedQty ON Tbl_Device.Device_ID = Tbl_ReceivedQty.Qty_Device_ID
where Qty_Date  between '2013-07-21' and '2013-07-23'

Posted 23-Jul-13 1:18am

ArunRajendra

Updated 23-Jul-13 1:28am

v2

Comments

$ultaNn 23-Jul-13 7:23am

Error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "d.Device_ID" could not be bound.

ArunRajendra 23-Jul-13 7:29am

Try the updated query in solution.

$ultaNn 23-Jul-13 7:38am

the query works but it cannot group by Qty_Device_ID
its repeating records

ArunRajendra 23-Jul-13 7:47am

Yes, if you are having different combination of Name,user and date for the same device id. In such case its not possible to get single records. It would be good if you could post sample data and expected output.

$ultaNn 23-Jul-13 7:54am

i am getting output like this
Device_Name Qty_Device_ID Qty_User Qty_Date SubTotal
HP Printer 100 Label 2013-07-21 11:40:31.667 450
HP LaserJet 101 Label 2013-07-21 11:40:40.027 450
KeyBoard 102 Label 2013-07-21 11:40:42.153 350
Dell Mouse 103 Label 2013-07-21 11:40:44.250 150
HP Printer 100 Label 2013-07-21 11:41:24.663 450
HP LaserJet 101 Label 2013-07-21 11:41:27.247 450
KeyBoard 102 Label 2013-07-21 11:41:29.620 350
HP Printer 100 Label 2013-07-22 14:28:10.257 450
HP LaserJet 101 Label 2013-07-22 14:28:13.620 450

$ultaNn 23-Jul-13 7:54am

i need only like this

Device_Name Qty_Device_ID Qty_User Qty_Date SubTotal
HP Printer 100 Label 2013-07-21 11:40:31.667 450
HP LaserJet 101 Label 2013-07-21 11:40:40.027 450
KeyBoard 102 Label 2013-07-21 11:40:42.153 350
Dell Mouse 103 Label 2013-07-21 11:40:44.250 150

$ultaNn 23-Jul-13 7:57am

what if frst fetch the data for between dates then sum of that data only ... ??? how can this be done ???

ArunRajendra 23-Jul-13 8:29am

You can remove the date column from the select and have it only in where clause.

$ultaNn 23-Jul-13 8:32am

It worked when remove date column..

$ultaNn 23-Jul-13 8:18am

Dear ArunRajendra the query failed . if i change the date then execute still its getting sum of whole record instead of getting sum so specific date records

ArunRajendra 23-Jul-13 8:22am

With date you will not get single record. If you want sub total for device id & Date the use query given in solution 2.