select with counting rows - 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 with counting rows

Author

Topic

koolnkool
Starting Member

9 Posts

Posted - 2009-10-01 : 03:32:55

I have 2 tables Licenses and LicensesToComputers.

1. Licenses (LicID, LicenseName, NumberOfLicenses)
2. LicenseToComputers (LCompID PK, LicID, CompID, IsActive)

Licenses (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:

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

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)

+ expand sourceview plaincopy to clipboardprint?

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

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

and I also have created a function fCountActive(@LicenseID)

·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150

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

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

Now it all works with this request:

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

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 writing a better way to select the data I want ?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 03:36:19

are you both same person?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133718

koolnkool
Starting Member

9 Posts

Posted - 2009-10-01 : 03:47:13

no but thank u 4 showing me a way

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 04:47:23

quote:
Originally posted by koolnkool

no but thank u 4 showing me a way

then how come you too posted same question?

are you both class mates?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-01 : 05:18:01

yea you just helped him with his coursework lol

quote:
Originally posted by visakh16

quote:
Originally posted by koolnkool

no but thank u 4 showing me a way

then how come you too posted same question?
are you both class mates?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources