Relate 3 tbls, do cnt on ech and outpt as 1 tbl

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

General SQL Server Forums

New to SQL Server Programming

Relate 3 tbls, do cnt on ech and outpt as 1 tbl

Author

Topic

hockeyman9474
Starting Member

11 Posts

Posted - 2013-10-17 : 14:27:53

I need help I'm am stuck an need to complete today. Here's the set up

I have 3 tables Confirmed, Released, and Dlr Inventory. Each can have upto 8 different models and upto 65 dealers that can have these models.Each line accounts for one vehicle. I imported each table as an .xls and appear like this:

[Ship To Dealer],[Car Line],Status

**The status us either Released, Dlr Inventory or Confirmed**

I need to show as:

[Ship To Dealer],[Car Line],Status,Count

I have tried Union all, derived tables, joins....you name it. I just can't get it.

A little help please.

Adam

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 14:52:08

The following is based on my understanding. But, if for example, ship to dealer column has distinct values in each row of the tables, this would give you count as one for every row - which is probably not what you want. Some sample data would help

SELECT 
	[Ship To Dealer],[Car Line],Status, COUNT(*) AS [Count]
FROM
(
	SELECT * FROM Confirmed
	UNION ALL
	SELECT * FROM Released
	UNION ALL
	SELECT * FROM [Dlr Inventory]
)s
GROUP BY [Ship To Dealer],[Car Line],Status

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-17 : 16:12:37

If James' guess doesn't solve your issue, please see the following links:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources