SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    UK
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple count() with where

    Hi

    table 'visitor': referrer | source | visit_type | id

    Example Data:

    Google UK | Adwords | 'U' | 9393945748
    Aol.com | Organic | 'R' | 567843355

    The target format I want to retrieve the data in is:

    referrer | source | unique visits | return visits

    I can retrieve the unique visits and group by referrer, source using:

    SELECT referrer, source COUNT(visit_type) AS unique_visits FROM visitor WHERE visit_type = 'U' GROUP BY referrer, source;

    I can also retrieve return visits using:

    SELECT referrer, source COUNT(DISTINCT id) AS return_visits FROM visitor WHERE visit_type = 'R' GROUP BY referrer, source;

    Is there a method to combine the two statements - IE get counts of both 'U' and 'R' in the one statement? I'm thinking perhaps a case, or simply using the 2 queries.
    Any help appreciated.

    Andy
    Last edited by andyr; Jul 28, 2005 at 06:37.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT referrer, 
    source, 
    COUNT(DISTINCT case when visit_type = 'R' then ID end) AS return_visits, 
    COUNT(case when visit_type = 'U' then 1 end) as unique_visits
    FROM visitor 
    GROUP BY referrer, source
    I changed the group by clase as there was some discrepancy.

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    UK
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks swampBoogie - it now works.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •