WHERE sys_Objects_Tags.SystemObjectID = @SystemObjectID
AND sys_Objects_Tags.SystemObjectRecordID = @SystemObjectRecordID
<div>I need to get a count in this statement of how many times a tag occurs and I think it needs a group by clause but I have been having trouble that like GROU BY sys_Objects_tags.TagID this is suppose to produce a single row of a TagName TagID and Count of
that Tag</div>
SELECT sys_Tags.TagName, COUNT(*)
FROM sys_Objects_Tags INNER JOIN sys_Tags
ON sys_Objects_Tags.TagID = sys_Tags.TagID
WHERE sys_Objects_Tags.SystemObjectID = @SystemObjectID
AND sys_Objects_Tags.SystemObjectRecordID = @SystemObjectRecordID
GROUP BY sys_Tags.TagName
Christopher Reed, MCT, MCPD, MCTS, Microsoft Specialist, MTA
"The oxen are slow, but the earth is patient."
Not quite, I need to get a single row of a tagname like Apple and number of occurances this is for multi blog site.
SELECT sys_Objects_Tags.TagID, sys_Tags.TagName,
(SELECT COUNT(*)
FROM sys_Objects_Tags
INNER JOIN sys_Tags ON sys_Objects_Tags.TagID = sys_Tags.TagID
WHERE sys_Objects_Tags.SystemObjectID = @BlogID
) // <- this column here need to be called "Count" I just need the count here of the tag
From sys_Objects_Tags
INNER JOIN sys_Tags ON
sys_Objects_Tags.TagID = sys_Tags.TagID
WHERE sys_Objects_Tags.SystemObjectID = @BlogID AND
sys_Objects_Tags.SystemObjectRecordID = @BlogEntryID
AND sys_Objects_Tags.TableName = 'Blog'
GROUP BY sys_Objects_Tags.TagID, sys_Tags.TagName
this code gives me a single row but it count up all the records at the moment Im have a brain freeze hah
kirkdm01
Member
6 Points
67 Posts
SQL Statement Help Too Tricky For Me
Jul 09, 2010 02:33 AM|LINK
Here is my SQL:
<div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">SELECT sys_Tags.TagName, sys_Tags.TagID, sys_Objects_Tags.TagID</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">FROM sys_Objects_Tags</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">INNER JOIN sys_Tags ON </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">sys_Objects_Tags.TagID = sys_Tags.TagID</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">WHERE sys_Objects_Tags.SystemObjectID = @BlogID</div> <div></div>SELECT sys_Tags.TagName, sys_Tags.TagID, sys_Objects_Tags.TagID
FROM sys_Objects_Tags
INNER JOIN sys_Tags ON
sys_Objects_Tags.TagID = sys_Tags.TagID
WHERE sys_Objects_Tags.SystemObjectID = @SystemObjectID
AND sys_Objects_Tags.SystemObjectRecordID = @SystemObjectRecordID
<div>I need to get a count in this statement of how many times a tag occurs and I think it needs a group by clause but I have been having trouble that like GROU BY sys_Objects_tags.TagID this is suppose to produce a single row of a TagName TagID and Count of that Tag</div>Careed
All-Star
18798 Points
3649 Posts
Re: SQL Statement Help Too Tricky For Me
Jul 09, 2010 03:02 AM|LINK
Try this:
SELECT sys_Tags.TagName, COUNT(*) FROM sys_Objects_Tags INNER JOIN sys_Tags ON sys_Objects_Tags.TagID = sys_Tags.TagID WHERE sys_Objects_Tags.SystemObjectID = @SystemObjectID AND sys_Objects_Tags.SystemObjectRecordID = @SystemObjectRecordID GROUP BY sys_Tags.TagName"The oxen are slow, but the earth is patient."
kirkdm01
Member
6 Points
67 Posts
Re: SQL Statement Help Too Tricky For Me
Jul 09, 2010 04:05 AM|LINK
Not quite, I need to get a single row of a tagname like Apple and number of occurances this is for multi blog site.
this code gives me a single row but it count up all the records at the moment Im have a brain freeze hah
Careed
All-Star
18798 Points
3649 Posts
Re: SQL Statement Help Too Tricky For Me
Jul 10, 2010 03:31 PM|LINK
Can you define what your tables, fields, and parameters represent? This should clarify what you're trying to accomplish with this query.
"The oxen are slow, but the earth is patient."
kirkdm01
Member
6 Points
67 Posts
Re: SQL Statement Help Too Tricky For Me
Jul 11, 2010 05:59 AM|LINK
I got my answer from another forum sweet as though
Hong-Gang Ch...
All-Star
74695 Points
6768 Posts
Re: SQL Statement Help Too Tricky For Me
Jul 14, 2010 08:16 AM|LINK
In order help other's, could you share your solution here?
Thanks.
If you have any feedback about my replies,please contactmsdnmg@microsoft.com.
Microsoft One Code Framework
kirkdm01
Member
6 Points
67 Posts
Re: SQL Statement Help Too Tricky For Me
Jul 15, 2010 12:24 AM|LINK
I created another INNER JOIN statement and then made anew select statement in brackets after that which gave me another column of Count