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.
Author |
Topic |
SergioM
Posting Yak Master
170 Posts |
Posted - 2013-07-29 : 11:44:06
|
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') > 0GROUP 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 IDcompanyName emailAddress ccAddress dailyMessages openTrackingSample Co Joe@sampleCo.com 4test@test.com 22 24Other Co steve@otherco.com test@test.com 34 24SuperCompany bob@supercompany.com test@test.com 11 24Just a company Bill@Justco.co test@test.com 32 24test test@gmail.com test@gmail.com 3 24L&D ldwholesale@gmail.com Sergio@clickgoandbuy.net 3 24My Company, LLC Roger@myco.org blah@gmail.com 4 24 -SergioI use Microsoft SQL 2008 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-29 : 12:25:03
|
[code]SELECT v.CompanyName, v.EmailAddress, v.ccAddress, v.DailyMessage, w.Items AS OpenTrackingFROM dbo.Vendor AS vINNER JOIN ( SELECT VendorID, COUNT(*) AS Items FROM dbo.Tracking WHERE pkgStatus <> '4' GROUP BY VendorID ) AS w ON w.VendorID = v.ID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2013-07-29 : 14:38:27
|
Wooot!!! Thank you!! I can't tell you how long I worked on this unsuccessfully.It works exactly as it should now :)-SergioI use Microsoft SQL 2008 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-29 : 15:10:17
|
[code]SELECT v.CompanyName, v.EmailAddress, v.ccAddress, v.DailyMessage, ISNULL(w.Items, 0) AS OpenTrackingFROM dbo.Vendor AS vLEFT JOIN ( SELECT VendorID, COUNT(*) AS Items FROM dbo.Tracking WHERE pkgStatus <> '4' GROUP BY VendorID ) AS w ON w.VendorID = v.ID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|