first field count display in second field

Question

first field count display in second field

sbrochu

SSC Enthusiast

Points: 171
More actions
October 7, 2010 at 2:18 pm

#157244

1. Please see this script. It works fine for Count
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]
*******************************************************
2. Please see this script below. When I run the script, I want to be able to count the first col and show the count in the second col. Is that possible?
for eg:
Name
C58186
C58186
C58186
C03721
C58069
C58069
C58069
C58069
Name Execution Count
C58186 3
C03721 1
C58069 4
Right now count is displaying 1 for all rows.
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
can some one please help me any better way to capture the count please?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

sbrochu (10/7/2010)
2. Please see this script below. When I run the script, I want to be able to count the first col and show the count in the second col. Is that possible?
for eg:
Name
C58186
C58186
C58186
C03721
C58069
C58069
C58069
C58069
Name Execution Count
C58186 3
C03721 1
C58069 4
Right now count is displaying 1 for all rows.

SELECT Name, [Execution Count] = count(*)

FROM [Unknown Table Name]

GROUP BY Name;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 2

sbrochu (10/7/2010)
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
can some one please help me any better way to capture the count please?
Thanks

Another option:

SELECT

T0.[Name] AS 'ComputerName',

--count( T0.[Name]) as 'Execution Count',

[Execution Count] = COUNT(*) OVER (PARTITION BY T0.[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'

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;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sbrochu SSC Enthusiast Points: 171 More actions · Answer 3

sbrochu

SSC Enthusiast

Points: 171

October 7, 2010 at 3:04 pm

#1233953

Thank you I so appreciate the help.

WayneS SSC Guru Points: 95409 More actions · Answer 4

Please be sure to come back and let us know how it worked out for you.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2