On 04-Jun-01 Harsh wrote:
> I have a table1 in mySQL with the following recordset :
>
> +--------+--------+---------+
>| siteno | deptid | agentid |
> +--------+--------+---------+
>| 0 | 27 | 35 |
>| 0 | 25 | 30 |
>| 0 | 19 | 30 |
>| 0 | 19 | 26 |
>| 0 | 20 | 26 |
>| 0 | 19 | 27 |
> +--------+--------+---------+
>
> Now I want to retrieve the distinct agentid's belonging to deptid 19
> along with their respective counts of how many departments do they
> belong to. Once again I repeat, I want to retrieve the agentids and
> their count of departments where the minimum condition to be
> satisfied
> is that agent must at least belong to deptid 19. Thus the resultset
> which I want to generate must look like this :
>
> +--------+---------+
>| count | agentid |
> +--------+---------+
>| 2 | 30 |
>| 2 | 26 |
>| 1 | 27 |
> +--------+---------+
>
> PS : The agent no 25 has not come because he does not hv any
> association
> with deptid 19.
>
> Can anybody pls give me the SQL query to generate the above resultset
> ?
>
SELECT count(b.dept) as cnt,a.agent as agent
FROM tbl as a LEFT JOIN tbl as b using (agent)
WHERE a.dept=19 group by agent
ORDER BY cnt desc , agent desc
+-----+-------+
| cnt | agent |
+-----+-------+
| 2 | 30 |
| 2 | 26 |
| 1 | 27 |
+-----+-------+
3 rows in set (0.03 sec)
select * from tbl;
+------+-------+
| dept | agent |
+------+-------+
| 27 | 35 |
| 25 | 30 |
| 19 | 30 |
| 19 | 26 |
| 20 | 26 |
| 19 | 27 |
+------+-------+
6 rows in set (0.02 sec)
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
