PDA

View Full Version : countif SQL view



Mike
16-02-2009, 03:19 PM
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.

dyewitness
16-02-2009, 03:46 PM
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

Mike
16-02-2009, 04:05 PM
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.

dyewitness
16-02-2009, 04:21 PM
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