Count more than one value?RSS

4 replies

Last post Feb 21, 2007 06:57 AM by Anonymous

  • Count more than one value?

    Feb 19, 2007 01:22 PM|Anonymous|LINK

    Damn, i just tried to edit my former post, but when i pressed submit, i lost almost an hour of writing

    i need help on this one, what i miss is how you count more than one event, for an example, i want to count querystrings.. one row for querystrings containing a surtain value "&nf_visits=0", the value Zero is the value i want to count seperately...and then a row for querystrings containing same value, but with any number (also 0), and finally these are grouped by a titleID ( which is in the cs-uri-query aswell like; &titleID=welcome&nf_visits=21& )

    so the out put would something like:

    TitleID            unique      visits
    -------------- ---------- --------
    welcome         45           651
    faq                122          785

    I can do this in two query's, but is it possible to do in just one query?..

     

    two single querys..this is one for total hits:

    SELECT DISTINCT TO_LOWERCASE(EXTRACT_VALUE(cs-uri-query, 'nf_tID')) AS TitleID, COUNT(*) FROM c:\logs\*.log
    WHERE cs-uri-stem = '/index.html' AND cs-uri-query LIKE '%nf_hitCount%' GROUP BY TitleID ORDER BY COUNT(*) DESC

    unique hits:

    SELECT DISTINCT TO_UPPERCASE(EXTRACT_VALUE(cs-uri-query, 'nf_tID')) AS TitleID, COUNT(*) FROM c:\logs\*.log
    WHERE cs-uri-stem = '/index.html' AND cs-uri-query LIKE '%nf_hitCount=0%' GROUP BY TitleID ORDER BY COUNT(*) DESC

     

     

    If i get this working, my next goal will be to make it possible to search within a date range.

    If you have any pointers, tips, examples on how to do this, please speak up... i really need this to work.  *<|=)

     

     

    W3C input format

  • Re: Count more than one value?

    Feb 20, 2007 07:09 AM|Anonymous|LINK

    I think this query is along the lines of what you are looking for:

    SELECT TitleID, UniqueHitCount, TotalHits
    USING
    TO_LOWERCASE(EXTRACT_VALUE(cs-uri-query, 'nf_tID')) AS TitleID
    , EXTRACT_VALUE(cs-uri-query, 'nf_hitCount') AS hitCount
    , SUM(CASE hitCount WHEN 0 THEN 1 ELSE 0 END) AS UniqueHitCount
    , COUNT(*) AS TotalHitCount
    FROM c:\logs\*.log
    WHERE cs-uri-stem = '/index.html'
    AND hitCount IS NOT NULL
    GROUP BY TitleID

    W3C input format

  • Re: Count more than one value?

    Feb 20, 2007 10:48 AM|Anonymous|LINK

    Thank you SO much for the reply, the script wont execute tho, it gives me following error:

    C:\Program Files\Log Parser 2.2>LogParser file:script6.sql -i:W3C -o:NAT
    Error: SELECT clause: Semantic Error: arguments of the WHEN statements must all
    be of the same type as the argument of the CASE statement

    sql:

    SELECT TitleID, UniqueHitCount, TotalHits
    USING
          TO_LOWERCASE(EXTRACT_VALUE(cs-uri-query, 'nf_tID')) AS TitleID
        , EXTRACT_VALUE(cs-uri-query, 'nf_hitCount') AS hitCount
        , SUM(CASE hitCount WHEN 0 THEN 1 ELSE 0 END) AS UniqueHitCount
        , COUNT(*) AS TotalHitCount
    FROM c:\logs\*.log
    WHERE cs-uri-stem = '/index.html'
        AND hitCount IS NOT NULL
    GROUP BY TitleID

    i wondered if you had to do a TO_INT on the hitCount alltho it IS a single number..maybe it does get treated as a string?..

     

    edit, "i wondered if you had to do a TO_INT " ..that didnt work =)

    W3C input format

  • Re: Count more than one value?

    Feb 20, 2007 11:23 AM|Anonymous|LINK

    FIXED:

    There was two minor things.. the 3rd row name "TotalHits" didnt match the one in the script, "TotalHitCount", second.. i guess the value of EXTRACT_VALUE(cs-uri-query, 'nf_hitCount') comes back as a string.. atleast it worked when i added the quotes ' '

    so, working script: (changes made, are in red)

    SELECT TitleID, UniqueHitCount, TotalHitCount
    USING
          TO_LOWERCASE(EXTRACT_VALUE(cs-uri-query, 'nf_tID')) AS TitleID
        , EXTRACT_VALUE(cs-uri-query, 'nf_hitCount') AS hitCount
        , SUM(CASE hitCount WHEN '0' THEN 1 ELSE 0 END) AS UniqueHitCount
        , COUNT(*) AS TotalHitCount
    FROM c:\logs\*.log
    WHERE cs-uri-stem = '/index.html'
        AND hitCount IS NOT NULL
    GROUP BY TitleID

    joy! ^_^

    Thank you so much Daniel Einspanjer, dont go too far, i'll probably need ur help again :o , but concidered this is the 3rd day of fiddling and knowing jack *** of SQL,  i'm pretty satisfied about where i am, thanks to you!.

    "Einspanjer", is that you real name? =).. does that mean "Loner" in your language?.. in my language it does!, "Enspænder", a guy who does things in his own way.

    Cheers, Jot 

     

    W3C input format

  • Re: Count more than one value?

    Feb 21, 2007 06:57 AM|Anonymous|LINK

    Very glad to hear you got it working. I'm working on a Linux desktop now and haven't run LP in a while.

    Einspanjer is an old low dialect of German. It was a work name that means "one horse carriage"
    Looks like one of the literal Dutch definitions for enspænder is one-horse as well. I really like the loner translation though!

    W3C input format