newbie needs help with a complex 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

General SQL Server Forums

New to SQL Server Programming

newbie needs help with a complex query

Author

Topic

rpettigr
Starting Member

4 Posts

Posted - 2005-09-06 : 12:40:16

I am new to SQL but I have jumped into it fairly deep. I need to write a query to generate a report that counts systems by the first two characters in the system name and gives a count by the first two characters and a total of all the systems.
The first part I have but I cannot think of how to get an overall total.
Here is the code I am using:
select
substring(dm.[Name],1,2) as 'Location',
count(distinct dm.[Name]) as '# Discovered',
count(distinct w.[Name]) as '# Managed',
count(distinct w.[Name])*100 / count(distinct dm.[Name]) as '% Managed'
from DiscoveredMachines dm, Wrksta w

where datediff(dd,dm.[DiscoveryDate],getdate()) < 90
and dm.[Name] *= w.[Name]
and (dm.[Name] like 'AB%' or dm.[Name]like 'CD%' or dm.[Name] like 'AD%'
or dm.[Name]like 'CB%' or dm.[Name] like 'SX%'
or dm.[Name] like 'EX%')

group by substring(dm.[Name],1,2)

compute sum(count(distinct dm.[Name])),
sum(count(distinct w.[Name]))

The results look like:
Location # Discovered # Managed % Managed
AB 30 27 90
AD 15 15 100
CB 20 19 95
CD 20 20 100
EX 35 30 86
SX 10 9 90

And then a separate result group with

sum sum
130 120

Is there any way I can get the sum on the same result group?
AM I even making sense?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-06 : 13:45:51

One way is to UNION ALL 2 seperate statements together instead of using a compute clause. You should use the new join syntax. See if this works:
(the derived table is just so that the sums will be the last row without the OrderCol column showing up in the result set.)


select	[Location]
	,[# Discovered]
	,[# Managed]
	,[% Managed]
from	(--this derived table is not necessary for the union but just 
         --so I could include a computed column for sorting that won't show up in results
	select	1 as orderCol,
		substring(dm.[Name],1,2) as [Location],
		count(distinct dm.[Name]) as [# Discovered],
		count(distinct w.[Name]) as [# Managed],
		count(distinct w.[Name])*100 / count(distinct dm.[Name]) as [% Managed]
	from	DiscoveredMachines dm
	left join Wrksta w
			on dm.[Name] = w.[Name]
	where	datediff(dd,dm.[DiscoveryDate],getdate()) < 90
	and	(dm.[Name] like 'AB%' or dm.[Name]like 'CD%' or dm.[Name] like 'AD%' 
		or dm.[Name]like 'CB%' or dm.[Name] like 'SX%' 
		or dm.[Name] like 'EX%')
	group by substring(dm.[Name],1,2)
	
	UNION ALL
	
	select	2
		,'total'
		,count(distinct dm.[Name])
		,count(distinct w.[Name])
		,null
		,null
	from	DiscoveredMachines dm
	left join Wrksta w
			on dm.[Name] = w.[Name]
	where	datediff(dd,dm.[DiscoveryDate],getdate()) < 90
	and	(dm.[Name] like 'AB%' or dm.[Name]like 'CD%' or dm.[Name] like 'AD%' 
		or dm.[Name]like 'CB%' or dm.[Name] like 'SX%' 
		or dm.[Name] like 'EX%')

	) a
order by orderCol, [# Discovered] desc

Be One with the Optimizer
TG

rpettigr
Starting Member

4 Posts

Posted - 2005-09-06 : 15:52:13

Many Thanks. I will give that a try.

rpettigr
Starting Member

4 Posts

Posted - 2005-09-06 : 18:27:50

Thanks a whole bunch TG. That did exactly what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources