Multiple Count Rows, Multiple Tables

[Cremate]

I want to count based on 1 (same) field from each of two tables, based on if ID is in "X".

select a.groupid, count(a.id), count(b.id)
from ta a, tb b
where value in ('a','b','c')
group by a.groupid

Thats sort of the psuedocode but Im not getting the right results.

Any help would be appreciated.

 

[Cremate]

bump ^^

 

[neileg]

There's no join in your pseudo code.

 

[Cremate]

the tables are not linked, but they have the same fields and represent a different occurrence (dont ask me I didnt create them).

I want to count the amount of times "UserID" is in one table and count the times it appears in the other table. I want to be able to count multiple users names (thus the in statement) and display them in a matrix, X by 3 (Userid, count-table1, count-table2).

Note I have managed to get them to display in a X by 2 table , but it seems the group by statement does work correctly with the union statement.

 

[neileg]

I can't debug your code if you're only sharing a snippet of it! If you have two tables in a query there must be a join otherwise you get a cartesian product where every record in each table is matched with every record in the other.

If I was doing this I would creat a query to count the IDs in one table, a second query for the other table and join these in a third query with a full list of all the possible IDs.

 

[Cremate]

You must spread some Reputation around before giving it to neileg again.

Thats what I ended up doing. I thought, perhaps, there was a single query that could round up that info, and there was a function/statement I was uninformed about.

Thanks again.

 

[neileg]

You can embed your subqueries into the overall query, but it just makes it neater, not more efficient.

Happy to help.