SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count of Group by?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

150 Posts

Posted - 07/29/2013 :  11:44:06  Show Profile  Reply with Quote
I want to join two tables and count the rows on the second table as something is grouped by the first. To be more clear. I have vendors and open tracking numbers for orders they have shipped. I want to list the vendor information and to group by the vendor. However I also want to count how many open orders that vendor has - which is on a different table. I have this so far:

SELECT `companyName`
	, `emailAddress`
	, `ccAddress`
	, `dailyMessages`
	, (SELECT count(*) FROM `tracking` WHERE `pkgStatus`!='4') AS 'openTracking' 
FROM `vendor` 
LEFT OUTER JOIN `tracking`
ON `vendor`.`id` = `tracking`.`vendorID`
WHERE (SELECT count(*) FROM `tracking` WHERE `pkgStatus`!='4') > 0
GROUP BY `vendor`.`id`


The problem is that this code results in this table. Where openTracking is always equal to the total count, not distinct to that vendor's ID

companyName	emailAddress	ccAddress	dailyMessages	openTracking
Sample Co	Joe@sampleCo.com	4test@test.com	22	24
Other Co	steve@otherco.com	test@test.com	34	24
SuperCompany	bob@supercompany.com	test@test.com	11	24
Just a company	Bill@Justco.co	test@test.com	32	24
test	test@gmail.com	test@gmail.com	3	24
L&D	ldwholesale@gmail.com	Sergio@clickgoandbuy.net	3	24
My Company, LLC	Roger@myco.org	blah@gmail.com	4	24


-Sergio
I use Microsoft SQL 2008

Edited by - SergioM on 07/29/2013 11:44:39

SwePeso
Patron Saint of Lost Yaks

Sweden
30212 Posts

Posted - 07/29/2013 :  12:25:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		v.CompanyName,
		v.EmailAddress,
		v.ccAddress,
		v.DailyMessage,
		w.Items AS OpenTracking
FROM		dbo.Vendor AS v
INNER JOIN	(
			SELECT		VendorID,
					COUNT(*) AS Items
			FROM		dbo.Tracking
			WHERE		pkgStatus <> '4'
			GROUP BY	VendorID
		) AS w ON w.VendorID = v.ID;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SergioM
Posting Yak Master

150 Posts

Posted - 07/29/2013 :  14:38:27  Show Profile  Reply with Quote
Wooot!!! Thank you!! I can't tell you how long I worked on this unsuccessfully.

It works exactly as it should now :)

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30212 Posts

Posted - 07/29/2013 :  15:10:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		v.CompanyName,
		v.EmailAddress,
		v.ccAddress,
		v.DailyMessage,
		ISNULL(w.Items, 0) AS OpenTracking
FROM		dbo.Vendor AS v
LEFT JOIN	(
			SELECT		VendorID,
					COUNT(*) AS Items
			FROM		dbo.Tracking
			WHERE		pkgStatus <> '4'
			GROUP BY	VendorID
		) AS w ON w.VendorID = v.ID;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000