June 9, 2011 at 1:58 pm
I have a table listing a bunch of things.
I have a sql query I wrote that will give me the data I want but I don't want just a listing of all the records. I want to list each unique entry in a field and then list next to it how many times it found that entry in that field.
here is the current query:
SELECT i.name
FROM (SELECT Guid, Name FROM vRM_Computer_Item WHERE ('%'='%' or LOWER ([Name]) LIKE LOWER ('%')))i
JOIN dbo.Inv_Installed_File_Details fd
ON fd._ResourceGuid = i.Guid
where fd.name = 'catalog.dat' and Path not like '%BinHub'
Order by i.name
So now I might have a bunch of names listed in field i.name:
Like
name:
bob
jim
james
bob
billy
james
bob
I would like to return
name total
bob - 3
jim - 1
james -2
billy 1
Is this possible???
I'm sure it is and I did some research and searching on these forums and now I'm left just to ask.
June 9, 2011 at 2:08 pm
SELECT i.name, Count(*) as Total
FROM (SELECT Guid, Name FROM vRM_Computer_Item WHERE ('%'='%' or LOWER ([Name]) LIKE LOWER ('%')))i
JOIN dbo.Inv_Installed_File_Details fd
ON fd._ResourceGuid = i.Guid
where fd.name = 'catalog.dat' and Path not like '%BinHub'
Group by i.name
Order by i.name
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 2:11 pm
thank you very much!
It was the group by that was keeping me from figuring it out.
June 10, 2011 at 6:28 am
One last question.
I want to add a join and a field from another db now.
How do I do this because I'm getting an error saying it is not a part of the aggregate function or the Group By?
New SQL
SELECT i.name as Server, ir.[Collection Time], Count(*) as Total
FROM (SELECT Guid, Name FROM vRM_Computer_Item WHERE ('%'='%' or LOWER ([Name]) LIKE LOWER ('%')))i
JOIN dbo.Inv_Installed_File_Details fd ON fd._ResourceGuid = i.Guid
JOIN dbo.Inv_Inventory_Results ir ON ir._ResourceGuid = i.Guid
where fd.name = 'catalog.dat' and Path not like '%BinHub'
Group by i.name
Order by total DESC, i.name
June 10, 2011 at 6:42 am
What do you want to do with that field?
Do you want the count per name and collection time, or do you want some function of the co0llection time for the name?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 6:47 am
for every name there is a single collection time.
Basically what happens in this is.
At xx time a collection is done against the name.
The reason I want the collection time added is it will tell me if for some reason the collection is failing for that name or failed that day for that name.
June 10, 2011 at 6:53 am
Add the column to the group by.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 7:27 am
Thank you this appears to work but I have found a new problem with the sql query even before I added collection time into the mix.
For some reason one of the machines is displaying a result of 6 when If I look at dbo.Inv_Installed_File_Details
only 3 results show.
This is where the collection is put and what I want to base my data off.
I can't figure out why it is doing this but it is.
June 10, 2011 at 8:32 am
Means one of your joins is not restrictive enough. If one table has 2 rows for a value, one table has 3 for a value and you join them on that column, the resultant resultset has 6 rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 9:21 am
ok my mistake I thought it happened before the fact that I added in the collection time but this is incorrect.
I found the solution to my issue but I don't know the best location to place this.
(select ir.[Collection Time] where ir.agent = 'Inventory Agent')
This limits the collection to only what I need. Here is what I got that appears to work. Opinion?
SELECT i.name as Server, ir.[Collection Time], Count(*) as Total
FROM dbo.Inv_Installed_File_Details fd
JOIN vComputer i ON i.Guid = fd._ResourceGuid
JOIN dbo.Inv_Inventory_Results ir ON ir._ResourceGuid = i.Guid
where fd.name = 'catalog.dat' and Path not like '%BinHub' and ir.agent = 'Inventory Agent'
Group by i.name, ir.[Collection Time]
Order by total DESC, i.name
June 10, 2011 at 9:55 am
Looks fine to me.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 12:46 pm
I do want to say thank you for all the help GilaMonster!
I'm a all in one admin so I can only keep up so much on my sql skills.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply