Another tricky bit of sql - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Another tricky bit of sql

Author

Topic

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-24 : 06:27:07

I've got a table that contains the [ClientID], [SearchEngine], [Keyword], [Page] and [Month] and am trying to create a stored procedure that will show the total listings per search engine for Page1,2,3 and 4+ based on a given month and client
eg
Data


                  Page  
SearchEngine1       2     
SearchEngine1       1     
SearchEngine1       2      
SearchEngine2       4      
SearchEngine3       3
SearchEngine4       2
SearchEngine5       1
SearchEngine5       1

Result wanted


                  Page1  Page2  Page3  Page4+
SearchEngine1       2       2     1      4
SearchEngine2       6       2     1      5
SearchEngine3       3       5     3      4

So far i have got

SELECT [SearchEngine],
Count([Page]) AS [Page1],
Count([Page]) AS [Page2],
Count([Page]) AS [Page3],
Count([Page]) AS [Page4+]
FROM [tRawData]
WHERE [ClientID] = @ClientID
AND [Month] = @TheMonth
GROUP BY [SearchEngine]
ORDER BY [SearchEngine] ASC

The above is obviously wrong, can anyone help?

Cheers in advance

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-24 : 07:35:50

This is what you want http://www.sqlteam.com/item.asp?ItemID=2955

Damian

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-24 : 08:34:09

Thanks for the link, i'm nearly there

DECLARE @ClientID int
DECLARE @TheMonth int
DECLARE @Sql varchar(8000)
SET @ClientID = 191
SELECT @TheMonth = MAX(Month) FROM tRawData WHERE ClientID = @ClientID

SET @Sql = 'SELECT [SearchEngine] FROM [tRawData] WHERE [ClientID]='
SET @Sql = @Sql + cast(@ClientID as varchar(5))
SET @Sql = @Sql + ' AND [Month]='
SET @Sql = @Sql + cast(@TheMonth as varchar(5))
SET @Sql = @Sql + ' GROUP BY [SearchEngine] ORDER BY [SearchEngine] ASC'

EXECUTE crosstab @Sql, 'Count([Page])', '[Page]', '[tRawData]'

It returns all the values for [Page] as column names but I only require 1,2,3, and 4+, the 4+ is the tricky one. Also i'm not guaranteed to be returned 1,2,3,4 but need them there with zero as the value

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-24 : 09:19:39

I've just been reading http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html
Can I not do this using Static Crosstabs as I know what values I need for the column names?

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-24 : 09:45:33

Done it

SELECT DISTINCT [SearchEngine], 
isnull((SELECT COUNT([Page]) from [tRawData] where [Page] = 1 and [SearchEngine]=rd.[SearchEngine] AND [ClientID] = @ClientID AND [Month] = @TheMonth),0) as [Page1], 
isnull((SELECT COUNT([Page]) from [tRawData] where [Page] = 2 and [SearchEngine]=rd.[SearchEngine] AND [ClientID] = @ClientID AND [Month] = @TheMonth),0) as [Page2], 
isnull((SELECT COUNT([Page]) from [tRawData] where [Page] = 3 and [SearchEngine]=rd.[SearchEngine] AND [ClientID] = @ClientID AND [Month] = @TheMonth),0) as [Page3],
isnull((SELECT COUNT([Page]) from [tRawData] where [Page] >= 4 and [SearchEngine]=rd.[SearchEngine] AND [ClientID] = @ClientID AND [Month] = @TheMonth),0) as [Page4+]
FROM [tRawData] rd
WHERE [ClientID] = @ClientID 
AND [Month] = @TheMonth
GROUP BY [SearchEngine]
ORDER BY [SearchEngine] ASC

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-24 : 16:32:24

Sorry, a little bit of assumption on my part.
Usually if someone asks a crosstab question, any attempt at writing a query for them would be answered with "but it needs to be dynamic"

Glad you got it sorted out

Damian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-24 : 17:19:27

I'd recommend this approach:


SELECT [SearchEngine], 
  SUM(CASE WHEN Page=1 THEN 1 ELSE 0 END) as Page1,
  SUM(CASE WHEN Page=2 THEN 1 ELSE 0 END) as Page2,
  SUM(CASE WHEN Page=3 THEN 1 ELSE 0 END) as Page3,
  SUM(CASE WHEN Page>3 THEN 1 ELSE 0 END) as [Page4+]
FROM
  [tRawData] rd
WHERE [ClientID] = @ClientID 
AND [Month] = @TheMonth
GROUP BY [SearchEngine]
ORDER BY [SearchEngine] ASC

much shorter and should be much more efficient .

- Jeff

DavidRhodes
Starting Member

40 Posts

Posted - 2004-03-25 : 04:36:55

quote:

Originally posted by jsmith8858

I'd recommend this approach:


SELECT [SearchEngine], 
  SUM(CASE WHEN Page=1 THEN 1 ELSE 0 END) as Page1,
  SUM(CASE WHEN Page=2 THEN 1 ELSE 0 END) as Page2,
  SUM(CASE WHEN Page=3 THEN 1 ELSE 0 END) as Page3,
  SUM(CASE WHEN Page>3 THEN 1 ELSE 0 END) as [Page4+]
FROM
  [tRawData] rd
WHERE [ClientID] = @ClientID 
AND [Month] = @TheMonth
GROUP BY [SearchEngine]
ORDER BY [SearchEngine] ASC

much shorter and should be much more efficient .

- Jeff

Not quite what I want, I needed the number of columns COUNT() for Page1,2,3,4+ not the SUM()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-25 : 07:30:01

look at it more closely -- what value is it adding up ?

1

That's how you COUNT !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources