Stuck with total records query

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)

Stuck with total records query

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-04-04 : 05:54:59

Hi,

I've got this

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

I need to get the number in stock, i.e. when instock=1 and the total i.e. when instock=1 or 0

But the problem is i'm only showing records where instock=1 so my SUM(case when inStock=1 then 1 else 0 end) AS stock statement is useless.

Whats the best way to do this

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 05:59:03

i don't really understand. what total are you after? a sum of you count? grouped by something other than above?

i think it would be clearer with some sample data / expected results

Em

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-04 : 06:37:07

try one of these either remove the where condition or replace u r sum with SUM(1).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 11:29:36

If you want the total sum why do you restrict your result with fileter inStock=1 in first place? Can you specify your full requirement with some sample data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources