I think something with grouping

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

SQL Server 2005 Forums

Transact-SQL (2005)

I think something with grouping

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-19 : 06:18:00

Hi, I'm trying to make a tagcloud module, and I try this by modifying a SP which was actually used by the search engine of a CMS.

What it should do is return a list of words and the number of occurrences for the whole portal.

What is does is return a list of words and the number of occurrences for each module (contentitem) in the portal.

So when in module 1 the text "blablabla" occurs 2 times, and in module 2, the text "blablabla" occurs 1 time, the result of the sp is:

Word Occurrences
BlaBlaBla 2
... ...
BlaBlaBla 1
... ...

What I want it to do is:

Word Occurrences
BlaBlaBla 3
.... .....

The SP now looks like this, so what do I have to change:

ALTER procedure [dbo].[GetTags]
@PortalID int
AS
SELECT
sw.Word,
siw.Occurrences
FROM
dbo.SearchWord sw
INNER JOIN dbo.SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID
INNER JOIN dbo.SearchItem si ON siw.SearchItemID = si.SearchItemID
INNER JOIN dbo.Modules m ON si.ModuleId = m.ModuleID
LEFT OUTER JOIN dbo.TabModules tm ON si.ModuleId = tm.ModuleID
INNER JOIN dbo.Tabs t ON tm.TabID = t.TabID
WHERE (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
AND (t.IsDeleted = 0)
AND (m.IsDeleted = 0)
AND (t.PortalID = @PortalID)
ORDER BY siw.Occurrences DESC

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-19 : 06:34:48

You've to GROUP BY Word and get the SUM of occurences.

SELECT Word, SUM(Occurences)
FROM table
GROUP BY Word

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-19 : 06:38:50

ALTER procedure [dbo].[GetTags]
@PortalID int
AS
SELECT
sw.Word,
Sum(siw.Occurrences)
FROM
dbo.SearchWord sw
INNER JOIN dbo.SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID
INNER JOIN dbo.SearchItem si ON siw.SearchItemID = si.SearchItemID
INNER JOIN dbo.Modules m ON si.ModuleId = m.ModuleID
LEFT OUTER JOIN dbo.TabModules tm ON si.ModuleId = tm.ModuleID
INNER JOIN dbo.Tabs t ON tm.TabID = t.TabID
WHERE (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
AND (t.IsDeleted = 0)
AND (m.IsDeleted = 0)
AND (t.PortalID = @PortalID)

ORDER BY siw.Occurrences DESC
Group By sw.Word

Gives an incorrect syntax near the keyword 'Group'

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-19 : 06:39:48

And:

ALTER procedure [dbo].[GetTags]
@PortalID int
AS
SELECT
sw.Word,
Sum(siw.Occurrences)
FROM
dbo.SearchWord sw
INNER JOIN dbo.SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID
INNER JOIN dbo.SearchItem si ON siw.SearchItemID = si.SearchItemID
INNER JOIN dbo.Modules m ON si.ModuleId = m.ModuleID
LEFT OUTER JOIN dbo.TabModules tm ON si.ModuleId = tm.ModuleID
INNER JOIN dbo.Tabs t ON tm.TabID = t.TabID
WHERE (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
AND (t.IsDeleted = 0)
AND (m.IsDeleted = 0)
AND (t.PortalID = @PortalID)
GROUP By sw.Word
ORDER BY siw.Occurrences DESC

Gives:
Column "dbo.SearchItemWord.Occurrences" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-19 : 06:40:32

Oh, I get it:

ALTER procedure [dbo].[GetTags]
@PortalID int
AS
SELECT
sw.Word,
Sum(siw.Occurrences) as Occurrences
FROM
dbo.SearchWord sw
INNER JOIN dbo.SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID
INNER JOIN dbo.SearchItem si ON siw.SearchItemID = si.SearchItemID
INNER JOIN dbo.Modules m ON si.ModuleId = m.ModuleID
LEFT OUTER JOIN dbo.TabModules tm ON si.ModuleId = tm.ModuleID
INNER JOIN dbo.Tabs t ON tm.TabID = t.TabID
WHERE (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
AND (t.IsDeleted = 0)
AND (m.IsDeleted = 0)
AND (t.PortalID = @PortalID)
GROUP By sw.Word
ORDER BY Occurrences DESC

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-11-19 : 06:40:49

That worked, thanks :-)

The secret to creativity is knowing how to hide your sources. (Einstein)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:29:13

ORDER BY Sum(siw.Occurrences) DESC

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources