Noob question about counting results

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

Noob question about counting results

Author

Topic

kefr
Starting Member

1 Post

Posted - 2011-01-18 : 08:32:13

Hello. Been having a field day with the sccm sql database. Sadly I have no knowledge of sql really except for what basic googling's brought me.
I was trying to generate a count of how many of our systems have intel, nvidia or ati/amd graphics cards. I've tried different things, but the closest I've gotten to something working, is a script that counts all of the same types of graphics cards. Sadly that's still a list of 103 different ones (based on windows driver name).
Why can't I make a sum() with a count() inside? how else can I get what I need.
What I want is to have it return something similar to :

Nvidia Intel Amd
102 604 815

Below are three samples of what I've tried :

SELECT 
(select count(*) where Video.Name0 like '%nvidia%') as [Nvidia],
(select count(*) where Video.Name0 like '%intel%') as [Intel], 
(select count(*) where Video.Name0 like '%ati%' or Video.Name0 like '%amd%')  as [Amd]
FROM v_GS_VIDEO_CONTROLLER Video  
inner join v_R_System sys on Video.ResourceID=sys.ResourceID




SELECT 
[vendor] = case
when Video.Name0 like '%nvidia%' then 'Nvidia'
when Video.Name0 like '%intel%' then 'Intel'
when Video.Name0 like '%ati%' or Video.Name0 like '%amd%' then 'Amd'
else 'Other'
end
FROM v_GS_VIDEO_CONTROLLER Video  
inner join v_R_System sys on Video.ResourceID=sys.ResourceID


SELECT 
sum((select count(*) where Video.Name0 like '%nvidia%')) as [Nvidia],
sum((select count(*) where Video.Name0 like '%intel%')) as [Intel], 
(select count(*) where Video.Name0 like '%ati%' or Video.Name0 like sum('%amd%'))  as [Amd]
FROM v_GS_VIDEO_CONTROLLER Video  
inner join v_R_System sys on Video.ResourceID=sys.ResourceID

Any help appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 08:36:18

SELECT
sum(case when Video.Name0 like '%nvidia%' then 1 else 0 end) as Nvidia
sum(case when Video.Name0 like '%intel%' then 1 else 0 end) as intel
sum(case when Video.Name0 like '%ati%' then 1 else 0 end) as amd
FROM v_GS_VIDEO_CONTROLLER Video
inner join v_R_System sys on Video.ResourceID=sys.ResourceID

Madhivanan

Failing to plan is Planning to fail

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-01-18 : 08:44:36

Better to have it this way and transform in the front end:



SELECT
	NoOfCards=COUNT(S.*),
	CardManufacturer=COALESCE(CardManufacturer,'Other')
FROM
	v_R_System S
		JOIN
			v_GS_VIDEO_CONTROLLER V ON
				V.ResourceID=S.ResourceID
		LEFT JOIN
			(
				SELECT CardManufacturer='NVidia' 
				UNION ALL
				SELECT 'Intel'
				UNION ALL
				SELECT 'ATI'
					
			) M	ON
				V.Name0 like '%'+ M.CardManufacturer +'%'
GROUP BY COALESCE(CardManufacturer,'Other')

It'll be easier to link to other queries if needed and easier to add more card manufactures to the list. You'll find keeping an attribute, like card manufacturer, as a column is the way SQL is designed to be used and is a good practice to stick to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources