I want to use countif function in sql.

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

I want to use countif function in sql.

Author

Topic

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-06 : 17:28:55

hello,
Please see the select statement below. When I run the script, there are duplicate names (computernames). I want to be able to count the first field and show the count in the second field. Is that possible?

for eg:

Name
C58186
C58186
C03721
C58069

Name Execution Count
C58186 2
C58186
C03721 1
C58069 1

SELECT
T0.[Name] AS 'ComputerName',
--count( T0.[Name]) as 'Execution 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/01/10'
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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-06 : 18:34:30

If you want this output instead, then just apply an aggregate function to the Name column such as MAX:

Name Execution Count
C58186 2
C03721 1
C58069 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-07 : 09:02:28

I want both columns for output. Can you show some examples for that please?

Thanks

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-07 : 09:42:25

I am trying this but getting an error
Each Group By Expression must contain at least one column that is not an outer reference.
I am unable to understand what it means.

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])
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

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-07 : 10:21:30

Please ignore my previous post.
I have this modified. I have a minor issue this time. I need help with.

SELECT
T1.[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 T1.[Name]

in (SELECT
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
T1.[Name])

Group by
T1.[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

SarahBrochu
Starting Member

13 Posts

Posted - 2010-10-07 : 10:23:57

The error I get is Conversion failed when converting the nvarchar value to data type int
The field is T0.Name
--hits

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources