Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: This is one (tricky) SQL query to do, anybody?

    Hi

    Can someone please help with this ?

    heres the sample data

    Workshop College Dept P_ID
    e-Training Business CIS 2
    e-Training Law OE 1
    e-Training Business CIS 5
    Tax Forms Business CIS 5
    Tax Forms Law OE 3
    Tax Forms Law OE 4
    Tax Forms Business CIS 2


    Notice that some P_ID are repeating but i just want a query "that tells me X number of people from this dept in this college have an entry"

    The results i want are:

    College Dept CountOfPID

    Business CIS 2
    Law OE 3

    that is, count of DISTINCT P_ID grouped by college, dept.

    thanks!

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: This is one (tricky) SQL query to do, anybody?

    Originally posted by atl-choozang
    Hi

    Can someone please help with this ?

    heres the sample data

    Workshop College Dept P_ID
    e-Training Business CIS 2
    e-Training Law OE 1
    e-Training Business CIS 5
    Tax Forms Business CIS 5
    Tax Forms Law OE 3
    Tax Forms Law OE 4
    Tax Forms Business CIS 2


    Notice that some P_ID are repeating but i just want a query "that tells me X number of people from this dept in this college have an entry"

    The results i want are:

    College Dept CountOfPID

    Business CIS 2
    Law OE 3

    that is, count of DISTINCT P_ID grouped by college, dept.

    thanks!

    Try this:

    Code:
    SELECT college, dept, count(distinct(p_ID))
    from your_table
    group by college, dept

  3. #3
    Join Date
    Feb 2004
    Posts
    19
    Hi

    I tried this before posting here but Access gives an error saying DISTINCT(P_ID) is not a valid function.

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by atl-choozang
    Hi

    I tried this before posting here but Access gives an error saying DISTINCT(P_ID) is not a valid function.
    Yea I wasnt sure it would work either...Its valid in Oracle, but I hadn't tested it in Access. I'll keep thinking and see if I can come up with a workaround.

  5. #5
    Join Date
    Feb 2004
    Posts
    19
    Ok i got the solution from aspfree.com :

    Where you want to use this query in Access or ASP. If it is in Access then
    Create Query1 as and save it.

    SELECT DISTINCT college, dept, PID
    FROM <TableName>
    ORDER BY college, dept, PID;


    Now create another query as

    SELECT Query1.college, Query1.dept, Count(Query1.PID) AS CountOfPID
    FROM Query1
    GROUP BY Query1.college, Query1.dept;


    this works perfect, but i couldnt combine both the queries into 1, i have a n-tuple effect, how can i create the perfect JOIN ?

    anybody?

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Have you tried

    SELECT a.college, a.dept, Count(a.PID) AS CountOfPID
    FROM (SELECT DISTINCT b.college, b.dept, b.PID
    FROM <TableName> b
    ORDER BY college, dept, PID) a
    GROUP BY a.college, a.dept, count(a.PID);

    ?
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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