Post reply

Sub Select Question

  • June 10, 2005 at 9:38 am

    #66137

    I have a simple table structure like so

    FieldName

    ID (PK)

    Keywordvarchar(50)

    SearchEnginevarchar(50)

    SourceString varchar(50)

    LEADchar(1)

    Data comes back like this

    1

    testkeyword

    testsearchengine

    testsoursestring

    Y

    or

    1

    testkeyword

    testsearchengine

    testsoursestring

    NULL

    What I need to do is get the total number of Leads grouped by Keyword, SearchEngine and SourceString

    SELECT Keyword, SearchEngine, SourceString, Count(*) As NumLeads FROM hit_tracker WHERE LEAD='Y' Group By Keyword, SearchEngine, SourceString

    So, that works fine, but what I need to add is a column that tells me the total number of hits WITHOUT caring if a LEAD was generated or not. The above SQL would tell me I had 1 lead generated from testkeyword, testsourcestring and testsearchengine. But how can I say I got two hits from that?? Make sense. Hope so. Thanks

    HC

  • June 10, 2005 at 9:46 am

    #564654

    This is as nice exemple where there's no need to beat around the bush.

    Declare @Total as int

    set @Total = (Select count(*) from dbo.Hit_Tracker)

    SELECT Keyword, SearchEngine, SourceString, Count(*) As NumLeads, @Total as Total FROM hit_tracker WHERE LEAD='Y' Group By Keyword, SearchEngine, SourceString

    --not sure it needs a group by but in case you need it, unquote:

    --, @Total

    Btw, there's a more efficient way of counting that number, but there's no guarantee about accuracy. Can you live with an approximation or do you need a correct count(*)?

  • June 10, 2005 at 9:55 am

    #564659

    Well, that just gives me the total number of leads in the DB. I need the total number of leads per lead type so to speak. Again, an example

    Keyword SearchEngine SourceString Lead

    keyword google longsourcestring Y

    keyword yahoo longsourcestring Y

    keyword google longsourcestring NULL

    So, I would need this to return

    Keyword SearchEngine SourceString Entries NumLeads

    keyword google longsourcestring 2 1

    keyword yahoo longsourcestring 1 1

    See, the first row, searchengine google has TWO entries, but just one Lead. The Sql you suggest would give me 3 entries for both rows. Any other thoughts? Thanks

  • June 10, 2005 at 10:02 am

    #564664

    SELECT Keyword, SearchEngine, SourceString, SUM(CASE WHEN LEAD='Y' THEN 1 ELSE 0 END) As NumLeads, COUNT(*) AS TotalHits FROM hit_tracker Group By Keyword, SearchEngine, SourceString

    This works...Thanks for the help.

  • June 10, 2005 at 10:04 am

    #564666

    That's what I was about to write.

    Nice job finding this yourself.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply