select count - 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)

select count

Author

Topic

jackie_sean
Starting Member

4 Posts

Posted - 2009-09-30 : 16:41:58

I have 2 tables Licenses and LicensesToComputers.


License (LicID, LicenseName, NumberOfLicenses)
LicenseToComputers (LCompID PK, LicID, CompID, IsActive)

If I want to get all computers for a given license, I would do something like this:

select L.LicenseName, LC.CompID from Licenses L
left join LicenseToComputers LC on LC.LicID = L.LicID

but I want to get
LicenseName .... count all computers for that license that have IsActive = 1 ... count all computers for that license regardless

I have created a function fCountAll(@LicenseID)


select count(*) from LicenseToComputers where LicID = @LicenseID

and I have created a function fCountActive(@LicenseID)


select count(*) from LicenseToComputers where LicID = @LicenseID and IsActive = 1

Now it all works with this request:


select LicenseName, fCountActive(LicID), fCountAll(LicID) 
from Licenses

I am afraid this is the worst and most inefficient way to achieve the result. My question is could you help me with a better way to select the data I want ?
Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-30 : 20:04:05

[CODE]
select
l.LicenseName,
count(*) CountAll,
sum(IsActive) CountActive -- Assumes that IsActive in (0, 1) or NULL
from
License l
inner join
LicenseToComputers ltc
on l.LicID = ltc.LicID
where
l.LicID = @LicenseID
group by
i.LicenseName
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources