Subquery in select statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery in select statement

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-14 : 09:11:34

Hi,

Got this query

SELECT vehicleref, capID, make, model, derivative, COUNT(vehicleref) AS stock FROM tblMatrixStock GROUP BY vehicleref, capID, make, model, derivative

Now for my stock count I only want to count them if they are in stock. So I tried this

SELECT vehicleref, capID, make, model, derivative, (COUNT(vehicleref) WHERE inStock=1) AS stock FROM tblMatrixStock GROUP BY vehicleref, capID, make, model, derivative

But it gives an error. How can I do it?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-14 : 09:41:08

SELECT vehicleref, capID, make, model, derivative,
SUM(case when inStock=1 then 1 else 0 end) AS stock
FROM tblMatrixStock
GROUP BY vehicleref, capID, make, model, derivative

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-14 : 09:42:55

or

SELECT vehicleref, capID, make, model, derivative, COUNT(vehicleref) AS stock FROM tblMatrixStock WHERE inStock=1
GROUP BY vehicleref, capID, make, model, derivative

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources