count function - 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 function

Author

Topic

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-07 : 11:24:30

I use this script with just two select fields for output. It works fine.

SELECT
T0.[Name] AS 'Name',
count(T1.[Name])

FROM ( [vResourceEx] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON
T0.[Guid] = T1.[_ResourceGuid] )
INNER JOIN [Evt_AeX_Application_Start] T2
ON
T1.[_ResourceGuid] = T2.[_ResourceGuid]
WHERE T2.[Policy Name] like '%adobe%'
AND T2.[Start Date] > '7/01/10'
AND
T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'
Group by
T0.[Name]

*********************************************************************

But this script I am trying with a subquery to get more fields and i get this error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value 'WindowsServer2003.WindowsXP-KB950749-x64-ENU.exe' to data type int.

This is my script. Can some one help me with this? Thanks

SELECT
T0.[Name] AS 'Name',
T1.[Domain] AS 'Domain',
T1.[Last Logon User] AS 'Last Logon User',
T2.[Start Date] AS 'Start Date',
T2.[File Name] AS 'File Name',
T2.[File Path] AS 'File Path',
T2.[FileVersion] AS 'FileVersion',
T2.[Policy Name] AS 'Policy Name'
FROM ( [vResourceEx] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON
T0.[Guid] = T1.[_ResourceGuid] )
INNER JOIN [Evt_AeX_Application_Start] T2
ON
T1.[_ResourceGuid] = T2.[_ResourceGuid]
WHERE T2.[Policy Name] like '%adobe%'
AND T2.[Start Date] > '7/01/10'
AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'
and T0.[Name]

in (SELECT
count(T0.[Name])
FROM ( [vResourceEx] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON
T0.[Guid] = T1.[_ResourceGuid] )
INNER JOIN [Evt_AeX_Application_Start] T2
ON
T1.[_ResourceGuid] = T2.[_ResourceGuid]
WHERE T2.[Policy Name] like '%adobe%'
AND T2.[Start Date] > '7/01/10'
AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'

Group by
T0.[Name])

Group by
T0.[Name],
T1.[Domain],
T1.[Last Logon User],
T2.[Start Date],
T2.[File Name],
T2.[File Path],
T2.[FileVersion],
T2.[Policy Name]

ORDER BY T2.[Start Date] DESC

SQLPillai
Starting Member

2 Posts

Posted - 2010-10-07 : 11:41:42

You are trying to match a varchar to an integer with your "and T0.[Name]

in (SELECT
count(T0.[Name])"

Not sure whether that was what you wanted. And it would error.

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-07 : 13:47:21

All I want is the count of the first column (Name)in my second column (count)
I tried this but the count output is 1 for all rows. can someone help me please?

SELECT
T0.[Name] AS 'Name',
count(T0.[Name]) as 'Count',
T1.[Domain] AS 'Domain',
T1.[Last Logon User] AS 'Last Logon User',
T2.[Start Date] AS 'Start Date',
T2.[File Name] AS 'File Name',
T2.[File Path] AS 'File Path',
T2.[FileVersion] AS 'FileVersion',
T2.[Policy Name] AS 'Policy Name'
FROM ( [vResourceEx] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON
T0.[Guid] = T1.[_ResourceGuid] )
INNER JOIN [Evt_AeX_Application_Start] T2
ON
T1.[_ResourceGuid] = T2.[_ResourceGuid]
WHERE T2.[Policy Name] like '%adobe%'
AND T2.[Start Date] > '7/07/10'
AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'
Group by
T0.[Name],
T1.[Domain],
T1.[Last Logon User],
T2.[Start Date],
T2.[File Name],
T2.[File Path],
T2.[FileVersion],
T2.[Policy Name]
ORDER BY T2.[Start Date] DESC

Gayathri.Varadarajan
Starting Member

2 Posts

Posted - 2010-10-08 : 02:58:06

Hi,
The problem is you are trying to compare the T0.[Name]
with count(T0.[Name])
in
AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'
and T0.[Name]

in (SELECT
count(T0.[Name])
FROM ( [vResourceEx] T0
The count subquery can be direclty used in the select statement as an inline function.you can avoid the group by clauses in the main query which would have been probably introduced due to the count clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources