dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL help needed (count query with two tables)

  1. #1
    Dave Guest

    SQL help needed (count query with two tables)


    I have two tables which both contain data on a user field. I want to count
    the users in the two tables in an sql query on an Access DB from VB6.

    I know that for a single table the query would be of the form:
    SELECT Count(User), User From MyTable GROUP BY User

    But for the two tables which contain different user access information I
    have been trying:
    SELECT Count(User), User From MyTable1 UNION SELECT Count(User), User From
    MyTable2 GROUP BY User

    But this didn't work at all and in fact brought up an error message.

    Is there a way to do this? I considered running one query using:
    SELECT User From MyTable1 UNION SELECT User From MyTable2
    and then trying to run another query:
    SELECT Count(User), User From ????? GROUP BY User
    but i'm not really sure what the ????? should be. ADO doesn't seem to give
    an option for querying another ADO recordset...

    Any ideas?
    Thanks in advance for any help,
    Dave



  2. #2
    Craig Brown Guest

    Re: SQL help needed (count query with two tables)


    Dave

    I tried this using a similar table structure we have and pulled back correct
    information... I hope it works on your DBMS...

    SELECT Count(UserID), UserId
    FROM MyTable1
    GROUP BY MyTable1.UserID
    UNION
    SELECT Count(UserID), UserId
    FROM MyTable2
    FULL OUTER JOIN MyTable2 ON (MyTable1.UserId=MyTable2.UserId)
    GROUP BY MyTable2.UserID

    Simply doing the Union Count on both tables was esy, it's getting the UserId
    field that created the problems...

    Craig Brown

    "Dave" <dgw567@yahoo.co.uk> wrote:
    >
    >I have two tables which both contain data on a user field. I want to count
    >the users in the two tables in an sql query on an Access DB from VB6.
    >
    >I know that for a single table the query would be of the form:
    >SELECT Count(User), User From MyTable GROUP BY User
    >
    >But for the two tables which contain different user access information I
    >have been trying:
    >SELECT Count(User), User From MyTable1 UNION SELECT Count(User), User From
    >MyTable2 GROUP BY User
    >
    >But this didn't work at all and in fact brought up an error message.
    >
    >Is there a way to do this? I considered running one query using:
    >SELECT User From MyTable1 UNION SELECT User From MyTable2
    >and then trying to run another query:
    >SELECT Count(User), User From ????? GROUP BY User
    >but i'm not really sure what the ????? should be. ADO doesn't seem to give
    >an option for querying another ADO recordset...
    >
    >Any ideas?
    >Thanks in advance for any help,
    >Dave
    >
    >



  3. #3
    YK Guest

    Re: SQL help needed (count query with two tables)



    Dave,

    You may try this with SQL server:


    Select trythis.userid, sum(trythis.subcount) as totalcount from
    (
    select userid, count(*) as subcount from table1 group by userid
    union all
    select userid, count(*) as subcount from table2 group by userid
    )
    as trythis
    group by trythis.userid



    For Oracle or Access, I believe you can omit the alias "trythis"


    yk


    "Dave" <dgw567@yahoo.co.uk> wrote:
    >
    >I have two tables which both contain data on a user field. I want to count
    >the users in the two tables in an sql query on an Access DB from VB6.
    >
    >I know that for a single table the query would be of the form:
    >SELECT Count(User), User From MyTable GROUP BY User
    >
    >But for the two tables which contain different user access information I
    >have been trying:
    >SELECT Count(User), User From MyTable1 UNION SELECT Count(User), User From
    >MyTable2 GROUP BY User
    >
    >But this didn't work at all and in fact brought up an error message.
    >
    >Is there a way to do this? I considered running one query using:
    >SELECT User From MyTable1 UNION SELECT User From MyTable2
    >and then trying to run another query:
    >SELECT Count(User), User From ????? GROUP BY User
    >but i'm not really sure what the ????? should be. ADO doesn't seem to give
    >an option for querying another ADO recordset...
    >
    >Any ideas?
    >Thanks in advance for any help,
    >Dave
    >
    >



  4. #4
    Dave Guest

    Re: SQL help needed (count query with two tables)


    yup, this worked great taking out the trythis. I actually tried modifying
    Craig's solution first but I couldn't get it to work with Vb and Access though.

    Thanks for the help guys,
    Dave

    "YK" <yk_hm@hotmail.com> wrote:
    >
    >
    >Dave,
    >
    >You may try this with SQL server:
    >
    >
    >Select trythis.userid, sum(trythis.subcount) as totalcount from
    >(
    >select userid, count(*) as subcount from table1 group by userid
    >union all
    >select userid, count(*) as subcount from table2 group by userid
    >)
    >as trythis
    >group by trythis.userid
    >
    >
    >
    >For Oracle or Access, I believe you can omit the alias "trythis"
    >
    >
    >yk
    >
    >
    >"Dave" <dgw567@yahoo.co.uk> wrote:
    >>
    >>I have two tables which both contain data on a user field. I want to count
    >>the users in the two tables in an sql query on an Access DB from VB6.
    >>
    >>I know that for a single table the query would be of the form:
    >>SELECT Count(User), User From MyTable GROUP BY User
    >>
    >>But for the two tables which contain different user access information

    I
    >>have been trying:
    >>SELECT Count(User), User From MyTable1 UNION SELECT Count(User), User From
    >>MyTable2 GROUP BY User
    >>
    >>But this didn't work at all and in fact brought up an error message.
    >>
    >>Is there a way to do this? I considered running one query using:
    >>SELECT User From MyTable1 UNION SELECT User From MyTable2
    >>and then trying to run another query:
    >>SELECT Count(User), User From ????? GROUP BY User
    >>but i'm not really sure what the ????? should be. ADO doesn't seem to

    give
    >>an option for querying another ADO recordset...
    >>
    >>Any ideas?
    >>Thanks in advance for any help,
    >>Dave
    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center