[RESOLVED]Multiple Row Count Results In Same Table

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)

[RESOLVED]Multiple Row Count Results In Same Table

Author

Topic

terbs
Starting Member

29 Posts

Posted - 2007-07-02 : 21:56:16

Ive created three row counts which work OK, but I need them to be returned as ONE table, how would I do this??


(
@COMPANYID integer
)

AS

SELECT COUNT(*) AS Branches
   
FROM  COMPANY

WHERE 

(
COMPANY.COMPANYID = @COMPANYID OR
COMPANY.CSASS = @COMPANYID
)

SELECT COUNT(*) As Clients
   
FROM  COMPANY

WHERE 

(
COMPANY.COMPANYID = @COMPANYID OR
COMPANY.VARASS = @COMPANYID
)

AND 

COMPANY.CENTRALSITE = 1

SELECT COUNT(*) AS Locations

FROM  COMPANY

WHERE 


COMPANY.VARASS = @COMPANYID

OR

COMPANY.CSASS = @COMPANYID

OR

COMPANY.COMPANYID = @COMPANYID

cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-02 : 22:21:53

[code]
CREATE PROCEDURE . . .
(
@COMPANYID integer
)
AS
BEGIN
DECLARE @Branch int,
@Clients int,
@Locations int

SELECT @Branch = COUNT(*) AS Branches
FROM COMPANY
WHERE
(
COMPANY.COMPANYID = @COMPANYID OR
COMPANY.CSASS = @COMPANYID
)

SELECT @Clients = COUNT(*) AS Clients
FROM COMPANY
WHERE
(
COMPANY.COMPANYID = @COMPANYID OR
COMPANY.VARASS = @COMPANYID
)
AND
COMPANY.CENTRALSITE = 1

SELECT @Locations = COUNT(*) AS Locations
FROM COMPANY
WHERE
COMPANY.VARASS = @COMPANYID
OR
COMPANY.CSASS = @COMPANYID
OR
COMPANY.COMPANYID = @COMPANYID

SELECT Branch = @Branch, Clients = @Clients, Locations = @Locations
END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

terbs
Starting Member

29 Posts

Posted - 2007-07-02 : 23:14:08

thanks khtan, thats what im after but it wont execute :(

"Msg 156, Level 15, State 1, Procedure sproc_CountBranches, Line 3
Incorrect syntax near the keyword 'DECLARE'.
Msg 156, Level 15, State 1, Procedure sproc_CountBranches, Line 7
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure sproc_CountBranches, Line 15
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure sproc_CountBranches, Line 25
Incorrect syntax near the keyword 'AS'."

sorry, im just hopeless with SQL

terbs
Starting Member

29 Posts

Posted - 2007-07-03 : 02:10:20

Did you enter your post khtan?, im still getting errors..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-03 : 02:13:17

Just edited it. Missed out the 'AS'

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 02:29:27

quote:

Originally posted by khtan


CREATE PROCEDURE . . .
(
@COMPANYID integer
)
AS
BEGIN
DECLARE @Branch		int,
	@Clients	int,
	@Locations	int

SELECT 	@Branch = COUNT(*) AS Branches
FROM  	COMPANY
WHERE 
(
	COMPANY.COMPANYID = @COMPANYID OR
	COMPANY.CSASS = @COMPANYID
)

SELECT 	@Clients = COUNT(*) AS Clients  
FROM  	COMPANY
WHERE 
(
	COMPANY.COMPANYID = @COMPANYID OR
	COMPANY.VARASS = @COMPANYID
)
AND 
	COMPANY.CENTRALSITE = 1

SELECT 	@Locations = COUNT(*) AS Locations
FROM  	COMPANY
WHERE 
	COMPANY.VARASS = @COMPANYID
OR
	COMPANY.CSASS = @COMPANYID
OR
	COMPANY.COMPANYID = @COMPANYID

SELECT	Branch = @Branch, Clients = @Clients, Locations = @Locations
END

KH
[spoiler]Time is always against us[/spoiler]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

terbs
Starting Member

29 Posts

Posted - 2007-07-03 : 02:34:00

great! thank you both khtan and harsh :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-03 : 02:46:01

Oh . . . thanks Harsh for the correction.

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 02:47:52

Never mind !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 03:24:38

Why three table scans?

CREATE PROCEDURE	dbo.uspSomeNameHere
(
	@COMPANYID INT
)
AS

SELECT	SUM(CASE WHEN @CompanyID IN (CompanyID, CSASS) THEN 1 ELSE 0 END) AS Branches,
	SUM(CASE WHEN @CompanyID IN (CompanyID, VARASS) AND CENTRALSITE = 1 THEN 1 ELSE 0 END) AS Clients,
	SUM(CASE WHEN @CompanyID IN (CompanyID, CSASS, VARASS) THEN 1 ELSE 0 END) AS Locations
FROM	COMPANY
WHERE	@CompanyID IN (CompanyID, CSASS, VARASS)
	OR CENTRALSITE = 1

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 03:41:09

Nice to see you back in action after long time, Peter!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 04:06:27

Yes, finally got internet access at home. Took them six weeks to install ADSL in our new house...

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 04:09:16

Oh..So finally you got the new house..Great!

Thought you got tired of guiding poor twits!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 04:12:46

I must admit that the count of interesting question and problems has gone down last two months.
Nowadays it seems the solution to most problems are found in Books Online with a simple search.

This however, was interesting.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 04:15:29

"Nowadays it seems the solution to most problems are found in Books Online with a simple search."

And most of people are not willing to do exactly that!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources