SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Durham, UK
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple 'COUNT' ?

    Hi,

    I was wondering if it's possible to 'COUNT' more than one column across multiple tables. I guess you should be able to but in the following SQL I get identical totals for both columns where I apply the 'COUNT'

    Code:
    SELECT 
     s.session_id, s.start_time, s.client_ip, COUNT(l.docid) cdoc, COUNT(sr.search_terms) csrch
    FROM 
     sub_sessions s, sub_logdoc l, sub_searches sr
    WHERE
     s.session_id = l.session_id
    AND
     s.session_id = sr.session_id
    AND s.customer_id = '" . $_GET["customers_id"] . "'
    GROUP BY session_id
    Is my GROUP clause totally wrong or is my SQL flawed elsewhere?

    Cheers

    Stew

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by stewartcuk
    Is my GROUP clause totally wrong or is my SQL flawed elsewhere?
    yes and yes

    your GROUP BY is wrong because it does not include
    all the non-aggregate columns from the SELECT

    your SQL is flawed because you appear to be trying to
    count values in tables whereas the way it works is that it
    counts values in the result set

    so for example if you have a result set that looks like this --

    S L SR
    1 a 23
    1 a 24
    1 a 25
    1 b 23
    1 b 24
    1 b 25
    1 c 21
    1 c 22
    2 a 33
    2 b 24
    2 b 25

    then what do you want your COUNTs to count?

    perhaps you might want to say COUNT(DISTINCT l.docid)
    and COUNT(DISTINCT sr.search_terms) instead?

    i can't tell because i don't know your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Durham, UK
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply... What I'm trying to say is "look at each user session and tell me how many documents they viewed and how many searches they made in each session". (that's me thinking aloud in pseudo code - not demanding anybody fixes my SQL )

    The three tables look like this

    {sub_sessions}
    Code:
    +-------------+-------------+------+-----+---------+-------+
    | Field       | Type        | Null | Key | Default | Extra |
    +-------------+-------------+------+-----+---------+-------+
    | customer_id | varchar(12) |      |     |         |       |
    | session_id  | varchar(32) |      |     |         |       |
    | start_time  | int(11)     |      |     | 0       |       |
    | client_ip   | varchar(16) |      |     |         |       |
    +-------------+-------------+------+-----+---------+-------+
    {sub_searches}

    Code:
    +--------------+--------------+------+-----+---------+-------+
    | Field        | Type         | Null | Key | Default | Extra |
    +--------------+--------------+------+-----+---------+-------+
    | session_id   | varchar(32)  |      |     |         |       |
    | search_terms | varchar(255) |      |     |         |       |
    +--------------+--------------+------+-----+---------+-------+
    {sub_logdoc}
    Code:
    +------------+-------------+------+-----+---------+-------+
    | Field      | Type        | Null | Key | Default | Extra |
    +------------+-------------+------+-----+---------+-------+
    | session_id | varchar(32) |      |     |         |       |
    | docid      | varchar(12) |      |     |         |       |
    +------------+-------------+------+-----+---------+-------+
    Hope this helps

    Stew
    Last edited by stewartcuk; Jun 24, 2004 at 07:23.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i could show you all sorts of ways to do this in a database that supports subqueries...

    your two relationships are unrelated to each other, even though both searches and logdocs are related to sessions

    you'll just have to run two queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •