Count subquery in select statement

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)

Count subquery in select statement

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-23 : 10:03:32

I've got this

SELECT vehicleref, capID, make, model, derivative, SUM(case when inStock=1 then 1 else 0 end) AS stock, SUM(case when inStock=1 then 1 else 1 end) AS total, (SELECT dealer FROM tblMatrixDealers WHERE id=dealerid) As Dealer FROM tblMatrixStock GROUP BY vehicleref, capID, make, model, derivative, dealerid

It works fine, the aggregate function within give the current stock level and the original stock level.

Now I need the same statement but only to show the vehicles with a stock level > 0 - (or instock = 1)

I changed my statement to:

SELECT vehicleref, capID, make, model, derivative, SUM(case when inStock=1 then 1 else 0 end) AS stock, SUM(case when inStock=1 then 1 else 1 end) AS total, (SELECT dealer FROM tblMatrixDealers WHERE id=dealerid) As Dealer FROM tblMatrixStock WHERE inStock = 1 GROUP BY vehicleref, capID, make, model, derivative, dealerid

But then the two totals are the same (becuase inStock is always true)

How can I do it?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 10:27:28

[code]SELECT ms.vehicleref,
ms.capID,
ms.make,
ms.model,
ms.derivative,
SUM(ms.inStock) AS stock,
COUNT(*) AS total,
d.dealer
FROM tblMatrixStock AS ms
INNER JOIN tblMatrixDealers AS d ON d.id = ms.dealerid
GROUP BY ms.vehicleref,
ms.capID,
ms.make,
ms.model,
ms.derivative,
ms.dealerid,
d.dealer
HAVING SUM(ms.inStock) > 0[/code]

E 12°55'05.25"
N 56°04'39.16"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-23 : 12:17:59

Thanks, I get this

Operand data type bit is invalid for sum operator.

InStock is bit not integer, is that the problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 12:21:22

[code]SELECT ms.vehicleref,
ms.capID,
ms.make,
ms.model,
ms.derivative,
SUM(CASE WHEN ms.inStock = 1 THEN 1 ELSE 0 END) AS stock, -- You cannot sum BIT
COUNT(*) AS total,
d.dealer
FROM tblMatrixStock AS ms
INNER JOIN tblMatrixDealers AS d ON d.id = ms.dealerid
GROUP BY ms.vehicleref,
ms.capID,
ms.make,
ms.model,
ms.derivative,
ms.dealerid,
d.dealer
HAVING SUM(ms.inStock) > 0[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources