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
 Count of Group by?

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') > 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

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 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;[/code]


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

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 :)

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

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 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;[/code]


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

- Advertisement -