SQL issues .....Count with a where

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

SQL issues .....Count with a where

Author

Topic

Johnnybax1
Starting Member

5 Posts

Posted - 2011-12-01 : 23:55:08

Select Builder.CompanyName
,(Select Count(VendorID) From POVendor Inner Join Project ON POVendor.ProjectId = Project.ProjectID Where Project.BuilderId = Builder.BuilderID ) As TotalBids
,(Select Count(VendorID) From POVendor Inner Join Project ON POVendor.ProjectId = Project.ProjectID Where POVendor.Status In ('New', 'Bidding In progress') And Project.BuilderId = Builder.BuilderID) As ActiveBids
,(Select Count(VendorID) From POVendor Inner Join Project ON POVendor.ProjectId = Project.ProjectID Where POVendor.Status In ('Awarded') And Project.BuilderId = Builder.BuilderID) As AwardedBids
,(Select Sum(AwardedTotal) From POVendor Inner Join Project ON POVendor.ProjectId = Project.ProjectID Where POVendor.Status In ('Awarded') And Project.BuilderId = Builder.BuilderID) As AwardedTotal
From Builder

I need a "where condition --> from builder where count(TotalBids) > 10

Please help ......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 01:02:01

[code]
Select b.CompanyName,
p.TotalBids,
p.ActiveBids,
p.AwardedBids,
p.AwardedTotal
From Builder b
INNER JOIN (Select Project.BuilderId,
Count(VendorID) AS TotalBids,
Count(CASE WHEN POVendor.Status In ('New', 'Bidding In progress') THEN VendorID ELSE NULL END) AS ActiveBids,
Count(CASE WHEN POVendor.Status In ('Awarded') THEN VendorID ELSE NULL END) AS AwardedBids,
Sum(CASE WHEN POVendor.Status In ('Awarded') THEN AwardedTotal ELSE 0 END) AS AwardedTotal
From POVendor
Inner Join Project
ON POVendor.ProjectId = Project.ProjectID
group by Project.BuilderId
)p
ON p.BuilderId = b.BuilderID
WHERE p.TotalBids >0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources