Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Group by across tables

    I have two tables in one table callhist there records for every contact made to a particular prospect and the resulting disposition. In the other table (contacts) there are the records for each prospect containing information about the prospect.

    I need to results like this.
    Here are the columns I need to Display.

    Campaign Name(from Contacts Table)
    Dials (Select count(*) from calhist (based on parameters))
    Contacts(Select count(*) from calhist (based on parameters))
    Pres(Select count(*) from calhist (based on parameters))
    Sales(Select count(*) from calhist (based on parameters))

    and this is the way I would like it to be displayed

    Campaigname|Dials|Contacts|Pres|Sales

    and Help would be appreciated.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    First you should create your select query, taking into account all the WHERE parametrised clauses, but leaving aside any grouping. Then, on top of this query you should contruct your groupping query, in a "select from select" fashion. In the end you should have something like that:

    SELECT Sum(P.field1), Max(P.Field2),... P.KeyFieldx
    FROM (SELECT field1, field2, ... KeyFieldx FROM table1 INNER JOIN table2 on.... WHERE ..... = [Your Param:]) P
    Group by P.KeyFieldx

    M.

Posting Permissions

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