Forum Home
Press F1
 
Thread ID: 97441 2009-02-16 02:19:00 countif SQL view Mike (15) Press F1
Post ID Timestamp Content User
748275 2009-02-16 02:19:00 Hi All,

I am wanting to create a SQL view that compares two tables and does something similar to an excel Countif function to count the number of times a value in one table appears in a second table.

Can this be done, and how would I create the view to show this?

Table one has the record ID
Table two has its own id, plus reference to a table one ID (this reference is not unique - and is the field I want to count).

I want the view to list the Table One ID and the number of times it appears in Table two.

Cheers,
Mike. 		Mike (15)
748276 2009-02-16 02:46:00 If your data looks like this:



table1
------
id1
---
1
2
3
...

table2
------
id2 | id1
1 | 1
2 | 1
3 | 2
4 | 3
...


(where column table2.id1 refers to column table1.id1)

Then the query would be:


select id1, count(id1) as count_of_id1
from table2
group by id1 		dyewitness (9398)
748277 2009-02-16 03:05:00 select id1, count(id1) as count_of_id1
from table2
group by id1
Yeah that's it :D

Now if I want to include a couple of values from the table one (should be a 1:1 match to records in this query), how do I get them to show in the result as well? Sorry I just found out that this was part of the request, otherwise I would have included it in my original post :)

Cheers,
Mike. 		Mike (15)
748278 2009-02-16 03:21:00 select table2.id1, table1.<another_column>, count(table2.id1) as count_of_id1
from table2 inner join table1 on table2.id1 = table1.id1
group by table2.id1, table1.<another_column>
order by table2.id1 		dyewitness (9398)
1