query two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

query two tables

Author

Topic

Leonel
Starting Member

10 Posts

Posted - 2012-02-20 : 21:25:08

i have these two tables:

CREATE TABLE [dbo].[dbo_vPickDetails](
[customer_number] [nvarchar](30) NULL,
[company_name] [nvarchar](50) NULL,
[item_number] [nvarchar](31) NULL,
[description] [nvarchar](255) NULL,
[picked_quantity] [float] NULL,
[order_number] [nvarchar](38) NULL,
[order_status] [int] NULL,
[due_date] [datetime2](0) NULL,
[name] [nvarchar](100) NULL,
[unit_of_measure] [nvarchar](10) NULL,
[creation_date] [datetime2](0) NULL,
[ship_date] [datetime2](0) NULL,
[po_status_text] [nvarchar](255) NULL,
[request_quantity] [float] NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL
) ON [PRIMARY]
NOT

dbo_vInventoryCost](
[inventory_id] [int] NULL,
[quantity] [float] NULL,
[record_status] [smallint] NULL,
[company_id] [int] NULL,
[item_number] [nvarchar](31) NULL,
[description] [nvarchar](255) NULL,
[cost] [money] NULL,
[avgcost] [money] NULL,
[code] [nvarchar](30) NULL,
[siteDes] [nvarchar](255) NULL,
[site_name] [nvarchar](50) NULL,
[site_id] [int] NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL
) ON [PRIMARY

i would like a query to join these two tables and display the following data: from the table pickdetails: item_number, description, sum(picked_quantity),ship_date and from the Cost table just the cost.
if i run the following query i get this results:

select[dbo].[dbo_vPickDetails].item_number,[dbo].[dbo_vPickDetails].description ,[picked_quantity],[ship_date],cost
from [dbo].[dbo_vPickDetails],[dbo].[dbo_vInventoryCost]
where [dbo].[dbo_vPickDetails].item_number =[dbo].[dbo_vInventoryCost].item_number
GROUP BY [dbo].[dbo_vPickDetails].item_number,[dbo].[dbo_vPickDetails].description,
[dbo].[dbo_vPickDetails].picked_quantity,[dbo].[dbo_vPickDetails].ship_date,[dbo].[dbo_vInventoryCost].cost

20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 0 9/20/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 1 7/23/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 1 8/3/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 1 8/16/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 1 10/4/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 1 10/13/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 1 10/30/2010 0:00 265.33
20000 REM 200M Line/LocalPWD - Gene Remote cards ( Also for Nextel ) 2 8/16/2010 0:00 265.33

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-21 : 07:00:35

Use SUM on the picked_quantity column in the select list and remove that from the GROUP BY list.

SELECT
	[dbo].[dbo_vPickDetails].item_number,
	[dbo].[dbo_vPickDetails].description,
	SUM([picked_quantity]),
	[ship_date],
	cost
FROM
	[dbo].[dbo_vPickDetails],
	[dbo].[dbo_vInventoryCost]
WHERE
	[dbo].[dbo_vPickDetails].item_number = [dbo].[dbo_vInventoryCost].item_number
GROUP BY
	[dbo].[dbo_vPickDetails].item_number,
	[dbo].[dbo_vPickDetails].description,
	--[dbo].[dbo_vPickDetails].picked_quantity,
	[dbo].[dbo_vPickDetails].ship_date,
	[dbo].[dbo_vInventoryCost].cost

Leonel
Starting Member

10 Posts

Posted - 2012-02-22 : 16:54:32

i got the cost colum, but not the pick_quantity sum, i want also a range of date
where ship_date between 1-1-2011 and 12-31-2011
HRU-402 REMOTE CARD 11
HRU-402 REMOTE CARD 11
HRU-402 REMOTE CARD 44
HRU-402 REMOTE CARD 0
HRU-402 REMOTE CARD 11
HRU-402 REMOTE CARD 11
HRU-402 REMOTE CARD 11
HRU-402 REMOTE CARD 33
iwant a line .like this
HRU-402 REMOTE CARD 132 $265.33
any help please

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 17:11:29

[code]SELECT
[dbo].[dbo_vPickDetails].item_number,
[dbo].[dbo_vPickDetails].description,
SUM([picked_quantity]),
--[ship_date],
SUM(cost)
FROM
[dbo].[dbo_vPickDetails],
[dbo].[dbo_vInventoryCost]
WHERE
[dbo].[dbo_vPickDetails].item_number = [dbo].[dbo_vInventoryCost].item_number
GROUP BY
[dbo].[dbo_vPickDetails].item_number,
[dbo].[dbo_vPickDetails].description--,
--[dbo].[dbo_vPickDetails].picked_quantity,
--[dbo].[dbo_vPickDetails].ship_date,
--[dbo].[dbo_vInventoryCost].cost[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources