Join two tables but one involves a count

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 2005 Forums

Transact-SQL (2005)

Join two tables but one involves a count

Author

Topic

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-04 : 18:41:11

I am looking to join two tables together. Easy enough. But i need another query to show the sum of one column and another query to show the count(*) on another column.

Scenerio : We are looking to find all the parts that we have sold more that 20 of but have to be from more than 3 unique customers. On top of that in a different table tied to the 1st one through InvoiceID, we need to pull the date field because we just want to go back one year.

HistoryHeader.InvoiceID = SOLines.InvoiceID (ties the tables together)

HistoryHeaderHeader.InvoiceDate (Has to be a 1 year and >)(only field we need in this table)

HistoryHeaderLines.Partnumber (repeating, shows the number of unique purchases)

HistoryHeaderLines.QtyPickedUp (shows the quantity of the purchase)

I am just at a loss to how I can combine all the queries into one to get it to display:
Partnumber | Unique Orders(over3) | TotalQtyOrdered(over 20) (all in the last year).

Thanks,

Jarrod R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 18:46:43

Show tables structure and with sample data and expected output for faster help.

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-04 : 19:02:35

[PartsInvoiceHistoryHeader](
[InvoiceID] [int] NOT NULL,
[InvoiceDate] [datetime] NULL,

[dbo].[PartsInvoiceHistoryLines](
[InvoiceID] [int] NOT NULL,
[PartNumber] [varchar](20),
[QtyPickedUp] [float] NULL,
[SourceCode] [varchar](3),
[Description] [varchar](30),

sample data
--------------------------------
[PartsInvoiceHistoryHeader]
[InvoiceID] =837086, 837087, 837088
[InvoiceDate] = 8/9/2007 12:00:00 AM, 8/9/2007 12:00:00 AM

[PartsInvoiceHistoryLines]
[InvoiceID] = 837086, 837087, 837088
[PartNumber] =16097-0004, 94021-10020-0S, 249190
[QtyPickedUp] =NULL, 1, 3, 17
[SourceCode] = HO,SU,KA,HO
[Description] =4 CYCLE GAL, GASKET,DRIN PLUG

Hope that helps. I am kind of a noobie when it comes to joins, unions etc.

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-05 : 12:44:05

The final results should look like:

Partnumber | UniqueCustomerOrdered | TotalQtyOrdered

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:48:51

[code]SELECT [PartNumber],
COUNT([InvoiceID]) AS UniqueCustomerOrdered,
SUM([QtyPickedUp]) AS TotalQtyOrdered
FROM [dbo].[PartsInvoiceHistoryLines]
GROUP BY [PartNumber][/code]

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-05 : 13:01:39

Thank you so much. I need to tie this query to [dbo].[PartsInvoiceHistoryHeader] via the InvoiceID column. An on the [dbo].[PartsInvoiceHistoryHeader] I need it to only pull data that is > '2008/1/1' from the column [InvoiceDate]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 13:05:04

simple join will do


SELECT [PartNumber],
COUNT([InvoiceID]) AS UniqueCustomerOrdered,
SUM([QtyPickedUp]) AS TotalQtyOrdered
FROM [dbo].[PartsInvoiceHistoryLines] ihl
JOIN [dbo].[PartsInvoiceHistoryHeader] ih
ON ih.[InvoiceID]=ihl.[InvoiceID]
WHERE ih.[InvoiceDate]>'20080101'
GROUP BY [PartNumber]

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-05 : 15:33:30

visakh16 - You are the man! Thank you for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 23:29:19

welcome

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-09 : 16:10:30

Sigh, i'm back with another table to add to the equation.

I am using the same 2 tables as before (above) but adding a PartsInventory Table to show how many of each part number we currently have in stock and the last time we ordered the part.

This table setup is as follows (showing only relevent columns)

[dbo].[PartsInventory]
[PartNumber] [varchar](20)NOT NULL,
[SourceCode] [varchar](3) NOT NULL,
[OrderUnitQty] [float] NULL,
[SuggestedSellQty] [float] NULL,
[ReOrderMethod] [varchar](1) COLLATE
[OnHand] [float] NULL,
[Available] [float] NULL,
[AvailOnOrder] [float] NULL,
[Cost] [money] NULL,
[Retail] [money] NULL,
[LastSoldDate] [datetime] NULL,

I need the new output to Show...

SourceCode| PartNumber| Description | UniqueCustomerOrdered | TotalQtyPU | TotalQtySold | Availiable | AvailiableOnOrder | LastRecievedDate | Cost | Retail

PartsInventory.partnumber = PartsInvoiceHistoryLines.partnumber
PartsInventory.SourceCode = PartsInvoiceHistoryLines.SourceCode
Cost can be pulled from PartsInventory or PartsInvoiceHistorLines
Retail can be pulled from PartsInventory or PartsInvoiceHistoryLines

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-09 : 16:15:48

Also my current query before tieing the 3rd table in is as follows.

SELECT
PartsInvoiceHistoryLines.SourceCode
,PartsInvoiceHistoryLines.partnumber
,(PartsInvoiceHistoryLines.Description)
,COUNT(PartsInvoiceHistoryLines.InvoiceID)AS UniqueCustomerOrdered
,SUM(PartsInvoiceHistoryLines.QtyPickedUp) AS TotalQtyPU
,SUM(PartsInvoiceHistoryLines.QtySold) AS TotalQtySold
FROM PartsInvoiceHistoryLines
JOIN PartsInvoiceHistoryHeader
ON PartsInvoiceHistoryLines.InvoiceID = PartsInvoiceHistoryHeader.InvoiceID
WHERE PartsInvoiceHistoryHeader.InvoiceDate > '20080101' and PartsInvoiceHistoryLines.QtyPickedUp is not null OR PartsInvoiceHistoryLines.QtySold is not null
GROUP BY partnumber,SourceCode,Description
order by TotalQtyPU desc;

Just need to pull PartsInventory.Availiable, PartsInventory.AvailOnOrder, PartsInventory.Cost, PartsInventory.Retail into the query.

Rohwer3
Starting Member

12 Posts

Posted - 2008-12-10 : 14:09:57

Any Ideas on how to pull in another table into the equation?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:12:49

[code]SELECT
ihl.SourceCode
, ihl.partnumber
,(ihl.Description)
,COUNT(ihl.InvoiceID)AS UniqueCustomerOrdered
,SUM(ihl.QtyPickedUp) AS TotalQtyPU
,SUM(ihl.QtySold) AS TotalQtySold,
pi.[Available],
pi.AvailOnOrder,
pi.Cost,
pi.Retail
FROM [dbo].[PartsInvoiceHistoryLines] ihl
JOIN [dbo].[PartsInvoiceHistoryHeader] ih
ON ih.[InvoiceID]=ihl.[InvoiceID]
JOIN [dbo].[PartsInventory] pi
ON pi.[PartNumber]=ihl.[PartNumber]
WHERE ih.[InvoiceDate]>'20080101'
GROUP BY [PartNumber][/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources