COUNT HELP New to SQL - 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

COUNT HELP New to SQL

Author

Topic

CGTECH
Starting Member

7 Posts

Posted - 2007-06-21 : 22:04:38

How can I add a count statement to this to get a count report from SMS?

select SYS.Netbios_Name0, SYS.AD_Site_Name0, SF.FileVersion
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID
Where SF.FileName LIKE "APG.exe" and SF.FileVersion != "1.02.0167" and (SYS.AD_Site_Name0='XXXXXX' or SYS.AD_Site_Name0='XXXXXX') order by SYS.AD_Site_Name0, SF.FileVersion

Thank You

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 22:08:09

what is SMS ?

SELECT SYS.Netbios_Name0, SYS.AD_Site_Name0, SF.FileVersion, COUNT(*)
FROM v_GS_SoftwareFile SF
JOIN v_R_System SYS ON SYS.ResourceID = SF.ResourceID
WHERE SF.FileName LIKE "APG.exe" 
AND   SF.FileVersion != "1.02.0167" 
AND  (
	SYS.AD_Site_Name0 = 'XXXXXX' 
     OR SYS.AD_Site_Name0 = 'XXXXXX'
     ) 
GROUP BY SYS.Netbios_Name0, SYS.AD_Site_Name0, SF.FileVersion
ORDER BY SYS.AD_Site_Name0, SF.FileVersion

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

CGTECH
Starting Member

7 Posts

Posted - 2007-06-21 : 22:10:06

Microsoft System management Server

CGTECH
Starting Member

7 Posts

Posted - 2007-06-21 : 22:16:49

It returned an error :(

An error occurred when the report was run. The details are as follows:
Column 'v_R_System.Netbios_Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 8120

quote:

Originally posted by khtan

what is SMS ?

SELECT SYS.Netbios_Name0, SYS.AD_Site_Name0, SF.FileVersion, COUNT(*)
FROM v_GS_SoftwareFile SF
JOIN v_R_System SYS ON SYS.ResourceID = SF.ResourceID
WHERE SF.FileName LIKE "APG.exe" 
AND   SF.FileVersion != "1.02.0167" 
AND  (
	SYS.AD_Site_Name0 = 'XXXXXX' 
     OR SYS.AD_Site_Name0 = 'XXXXXX'
     ) 
GROUP BY SYS.Netbios_Name0, SYS.AD_Site_Name0, SF.FileVersion
ORDER BY SYS.AD_Site_Name0, SF.FileVersion

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 22:19:16

I amended the query and added GROUP BY which as missed out earlier

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

CGTECH
Starting Member

7 Posts

Posted - 2007-06-21 : 22:21:44

Ahhh, it returned a count just now but it was nest to each colum Hmmm

Is there a way to make it so the whole statement just gives the count report, rather than a count for each line?

Sorry for all of the questions and thank you!!

quote:
Originally posted by khtan

I amended the query and added GROUP BY which as missed out earlier

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 22:23:42

you want to know the number of rows returns or a summation of count for each line ?

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

CGTECH
Starting Member

7 Posts

Posted - 2007-06-21 : 22:27:05

Coding Verbiage escapes me sorry :)

If I have 5 lines, I would like the report to just show a number 5 that I can link the actual report to (does that make sense?) (SMS will auto add the link)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 22:29:24

[code]SELECT COUNT(*)
FROM v_GS_SoftwareFile SF
JOIN v_R_System SYS ON SYS.ResourceID = SF.ResourceID
WHERE SF.FileName LIKE "APG.exe"
AND SF.FileVersion != "1.02.0167"
AND (
SYS.AD_Site_Name0 = 'XXXXXX'
OR SYS.AD_Site_Name0 = 'XXXXXX'
)
[/code]

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

CGTECH
Starting Member

7 Posts

Posted - 2007-06-21 : 22:33:51

Beautiful! Thank You.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-22 : 09:08:44

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

CGTECH
Starting Member

7 Posts

Posted - 2007-06-22 : 19:38:29