Reputation: 79
I have two tables and I am trying to count the number of times a particular 'ID' occurs and then output a name that corresponds with that 'id'. For example, certain people within the list have multiple occurring IDs, and I want to output the people whose IDs occur more than 4 times.
Thanks to bvr, I have one part of it, I now know how to display the Values that occur more than 4 times, by using the following statement:
SELECT ID , COUNT(OrderID) FROM Table1
GROUP BY ID
HAVING COUNT(OrderID) > 4
Just need the second part.
Here is an example of the two tables.
Table 1
ID | OrderID
--------------
191 | 1020
150 | 1090
191 | 1023
140 | 1033
191 | 2132
191 | 1233
191 | 1321
Table 2
ID | FirstName
--------------
191 | Mat
150 | Stewart
191 | Mat
140 | John
191 | Mat
191 | Mat
191 | Mat
I want to output something like this
Occurrences | FirstName
-------------------------
5 | Mat
Upvotes: 2
Views: 100
Reputation: 1271241
You want to join to the second table to get the name:
SELECT t1.ID , COUNT(t1.OrderID), min(t2.name) as name
FROM Table1 t1 join
(select id, min(name) as name
from Table2 t2
group by id
) t2
on t1.id = t2.id
GROUP BY t1.ID
HAVING COUNT(t1.OrderID) > 4;
Before doing the join, though, you need to aggregate by id
to ensure that there are no duplicates. The subquery gets the minimum name on an id
. If all names are the same, this will be the name. Otherwise, it is an arbitrary name assigned to the id
.
EDIT:
You can do the query without subqueries, but I'm not sure that will be more efficient:
SELECT t1.ID , COUNT(distinct t1.OrderID), min(t2.name) as name
FROM Table1 t1 join
Table2 t2
on t1.id = t2.id
GROUP BY t1.ID
HAVING COUNT(distinct t1.OrderID) > 4;
This may look simpler, but it can be much worse in terms of performance. For instance, if one id had 1000 matches in both table 1 and table 2, then that id would end up with 1,000,000 rows to process after the join.
Upvotes: 1
Reputation: 92845
A version with DISTINCT
SELECT t2.FirstName, COUNT(*) Occurrences
FROM Table1 t1 JOIN
(
SELECT DISTINCT id, firstname
FROM Table2
) t2
ON t1.ID = t2.ID
GROUP BY t2.ID, t2.FirstName
HAVING COUNT(*) > 4
Output:
| FIRSTNAME | OCCURRENCES | --------------------------- | Mat | 5 |
Here is SQLFiddle demo
Upvotes: 0