Nested aggregated . Top Count queries

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)

Nested aggregated . Top Count queries

Author

Topic

graag
Starting Member

3 Posts

Posted - 2010-08-30 : 10:00:00

A table [keywords] contain the columns KeywordsText, KeywordsDate & CountryID

I would like to get the rows with popular keywords that were added recently (this week), but not earlier (last week)
I am getting an empty set instead

Here is what I tried, in short:


SELECT TOP (20) COUNT(Keywords.KeywordsText) AS LeCount where date range = [recent]
and not exists 
SELECT TOP (20) COUNT(Keywords.KeywordsText) AS LeCount2 where date range = [sometime in the past]

Here is the complete query:


SELECT        TOP (20) COUNT(Keywords.KeywordsText) AS LeCount,  Keywords.KeywordsText, Location_1.Title AS Country
FROM            Keywords LEFT OUTER JOIN
                         Location ON Keywords.LocationID = Location.LocationID LEFT OUTER JOIN
                         Location AS Location_1 ON Keywords.CountryID = Location_1.LocationID
WHERE     
    Keywords.KeywordsDate > @MinLastUpdatedDate
    and Keywords.KeywordsDate < @MaxLastUpdatedDate 
    and Keywords.CountryID = @CountryID
    and not exists ( 
        
        SELECT        TOP (20) COUNT(Keywords.KeywordsText) AS LeCount2,  Keywords.KeywordsText
        FROM            Keywords 
        WHERE     (Keywords.KeywordsDate > @NotIn_MinLastUpdatedDate  and Keywords.KeywordsDate < @NotIn_MaxLastUpdatedDate and Keywords.CountryID = @CountryID)
        
        GROUP BY Keywords.KeywordsText, Keywords.CountryID
        ORDER BY LeCount2 DESC        
    )
    
GROUP BY Keywords.KeywordsText, Keywords.CountryID, Location_1.Title
ORDER BY LeCount DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 10:03:27

you're taking top 20 also in last weeks case so what you'll be getting also includes those which happened in last week but not in top 20 list of last week.

also how will you be calculating values for variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 10:04:59

for this week take


Keywords.KeywordsDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 
    and Keywords.KeywordsDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)


for last week
Keywords.KeywordsDate >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0) 
    and Keywords.KeywordsDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

graag
Starting Member

3 Posts

Posted - 2010-08-30 : 12:05:56

Thank you for the quick replies

The dates are not an issue, I will pass them as parameters; this query is used to test several time frame (rising this week, this month, ...)

This is what I am trying to achieve:
This table logs a keyword every time a user searches for this keyword on a site.
I want to be able to find what keywords are popular by counting their occurrences (during the past week, the past month, ...)
as well as well other keywords that are starting to suddenly popular AND not appearing in the previous list

graag
Starting Member

3 Posts

Posted - 2010-08-31 : 08:53:07

Here is a simplified version of the query; still getting an empty set
Any idea?

SELECT        TOP (20) COUNT(Keywords.KeywordsText) AS LeCount,  Keywords.KeywordsText, Location_1.Title AS Country
FROM            Keywords LEFT OUTER JOIN
                         Location ON Keywords.LocationID = Location.LocationID LEFT OUTER JOIN
                         Location AS Location_1 ON Keywords.CountryID = Location_1.LocationID
WHERE     
    Keywords.KeywordsDate > @MinLastUpdatedDate
    and Keywords.KeywordsDate < @MaxLastUpdatedDate 
    and Keywords.CountryID = @CountryID
    and not exists ( 
        
        SELECT  Keywords2.KeywordsText
        FROM    Keywords as Keywords2 
        WHERE   (Keywords2.KeywordsDate > @NotIn_MinLastUpdatedDate  and Keywords2.KeywordsDate < @NotIn_MaxLastUpdatedDate and 
                    Keywords2.CountryID = @CountryID)    
    )
    
GROUP BY Keywords.KeywordsText, Keywords.CountryID, Location_1.Title
ORDER BY LeCount DESC

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-31 : 09:13:56

Could you provide tables with sample data and expected output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources