count() and sum() on multiple tables

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • philippezzz
    New Member
    • Jun 2007
    • 1

    count() and sum() on multiple tables

    I have 3 tables

    clients
    -cID //pk
    -cName

    visits
    -vID //pk
    -cID //client ID
    -vDate //format YYYY-MM-DD
    -vVisits //int

    forms
    -fID //pk
    -cID //client ID
    -fDate //format YYYY-MM-DD
    -fMessage

    My 3 Tables with Data

    clients
    //cID - cName
    1 - company1
    2- company2

    visits
    //vID - cID - vDate - vVisits
    1 - 1 - 2007-06-22 - 50
    2 - 1 - 2007-06-23 - 75
    3 - 2 - 2007-06-22 - 100
    4 - 2 - 2007-06-23 - 125
    5 - 2 - 2007-07-01 - 150
    6 - 2 - 2007-07-02 - 150

    forms
    //fID - cID - fDate - fMessage
    1 - 1 - 2007-06-22 - "Bob"
    2 - 1 - 2007-06-23 - "Ronald"
    3 - 2 - 2007-06-22 - "Mike"
    4 - 2 - 2007-06-23 - "Stan"

    I'm hoping to get the following result
    //company name (cName) - Group by Months - Group by Years - SUM of visits by months - COUNT of forms by months
    company1 - 6 - 2007 - 125 - 2
    company2 - 6 - 2007 - 225 - 2
    company2 - 7 - 2007 - 300 - 0


    Is it possible in a single query?
Working...