SQL Query problem - 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

General SQL Server Forums

New to SQL Server Programming

SQL Query problem

Author

Topic

bmwiest
Starting Member

4 Posts

Posted - 2009-11-19 : 17:50:44

I am writing this query for as report but the problem is I know the data it is presenting is wrong because some of the entries are duplicates. I have removed these duplicates in other queries by doing a select distinct on the name0 column but I cannot do that in this query. I believe this is because this is doing a count. Any ideas on how I can weed out the duplicate entries?

Select v_R_System.Resource_Domain_OR_Workgr0, count(Client0) as 'Agents'
from v_R_System
WHERE (Operating_System_Name_and0 LIKE '%Server%') AND (Client0 = 1)
group by v_R_System.Resource_Domain_OR_Workgr0
Order by v_R_System.Resource_Domain_OR_Workgr0

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 18:35:16

How the hell do you have duplicates with that?

Post some of the sample result set

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 01:48:02

Have you tried count(distinct Client0) ?

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 10:53:30

There is still NO WAY you are getting Dups

And what's with the 0's....a cut and paste problem?



CREATE TABLE #v_R_System99 (
	  Resource_Domain_OR_Workgr varchar(100)
	, Client int, Col3 datetime
	, Operating_System_Name_and varchar(256)
)
GO

INSERT INTO #v_R_System99 (Resource_Domain_OR_Workgr, Client, Col3, Operating_System_Name_and)
SELECT 'a', 1, GetDate(), 'myServer99'		UNION ALL
SELECT 'b', 1, GetDate(), 'myServer98'		UNION ALL
SELECT 'c', 2, GetDate(), 'myServer97'		UNION ALL
SELECT 'c', 1, GetDate(), 'myServer96'		UNION ALL
SELECT 'd', 3, GetDate(), 'myServer95'		UNION ALL
SELECT 'd', 4, GetDate(), 'myServer94'		UNION ALL
SELECT 'e', 1, GetDate(), 'LayDownSally'	UNION ALL
SELECT 'e', 1, GetDate(), 'myServer99'		UNION ALL
SELECT 'e', 1, GetDate(), 'HoTRodLincoln'	UNION ALL
SELECT 'e', 1, GetDate(), 'myServer99'		UNION ALL
SELECT 'e', 1, GetDate(), 'myServer99'
GO

   SELECT Resource_Domain_OR_Workgr
		, COUNT(Client) AS COUNT_Client
	 FROM #v_R_System99
	WHERE Operating_System_Name_and LIKE '%Server%' AND Client = 1
 GROUP BY Resource_Domain_OR_Workgr
GO

DROP TABLE #v_R_System99
GO


Resource_Domain_OR_Workgr                                                                            COUNT_Client
---------------------------------------------------------------------------------------------------- ------------
a                                                                                                    1
b                                                                                                    1
c                                                                                                    1
e                                                                                                    3

(4 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bmwiest
Starting Member

4 Posts

Posted - 2009-11-20 : 12:15:11

quote:
Originally posted by madhivanan

Have you tried count(distinct Client0) ?

Madhivanan

Failing to plan is Planning to fail

I tried this and since the client is either 0, 1 or NULL I got back 1 in all groups. didn't work thanks though.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 12:49:54

Why don't you post some sample data, and what you want to get as the result set

Sort of like what I just posted

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bmwiest
Starting Member

4 Posts

Posted - 2009-11-20 : 15:51:28

I will try to show you the best example I can.

Here is my Table
Resoruce ID, Resource_Domain_OR_Workgr0, Name0, Client0
1000012, SA, ServerA, 1
1000013, SA, ServerA, 1
1000014, NA, ServerB, 1
1000015, NA, ServerC, 1
1000016, NA, ServerB, 1
1000017, EU, ServerD, 1
1000018, EU, ServerE, 1
1000019, EU, ServerE, 1
1000020, EU, ServerD, 1

If I run the following Sql Query:
SELECT DISTINCT Name0 AS Name, Operating_System_Name_and0 AS [Operating System Name], Resource_Domain_OR_Workgr0 [Domain], Client0 [Client]
FROM v_R_System
WHERE (Operating_System_Name_and0 LIKE '%Server%') AND
(Resource_Domain_OR_Workgr0 LIKE 'NA') AND
(Client0 = 1)
ORDER BY Name

I should get NA = 2
If I take out the Distinct I get NA = 3

Now if I run:
Select v_R_System.Resource_Domain_OR_Workgr0, count(Client0) as 'Agents'
from v_R_System
WHERE (Operating_System_Name_and0 LIKE '%Server%') AND (Client0 = 1)
group by v_R_System.Resource_Domain_OR_Workgr0
Order by v_R_System.Resource_Domain_OR_Workgr0

I get:
SA = 2
NA = 3
EU = 4

I want to get:
SA = 1
NA = 2
EU = 2

I hope this explains it better, thanks for the help.

bmwiest
Starting Member

4 Posts

Posted - 2009-11-23 : 16:48:46

So i went to go get a soda today and I was talking to someone at the soda machine about this and then it dawned on me how to fix it. YES I fixed it I decided that since I was already had the query sorting client0 = 1 in the where command that I could count the Name0 column where I can run a distinct command code works as follows:

Select v_R_System.Resource_Domain_OR_Workgr0, count(distinct Name0) as 'Agents'
from v_R_System
WHERE (Operating_System_Name_and0 LIKE '%Server%') AND (Client0 = 1)
group by v_R_System.Resource_Domain_OR_Workgr0
Order by v_R_System.Resource_Domain_OR_Workgr0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources