* [EMAIL PROTECTED] > Having just walked into a job a few months back, one of the tasks I've > been asked to accomplish is to counting records inside a DB based on a > field. The database in questions holds realtor listings. There are fields > for Company Name and Agent Name on each listing. What I need to do is sort > records based on the Company Name and the itemize the number of ads per > Agent and show that in a report. The Agents may change from time to time > so keeping a table for a Company with only the agent names may not be a > solution. Is there a way to pull these records in this fashion in MySQL? > Like I said all the information is stored in one table with fields for > Company name, Agent name, price, city, county etc.. > > Example: > > Realty Company 1 > ------------------ > Agent 1 10 ads > Agent 2 8 ads > Agent 3 20 ads > > > Realty Company 2 > ------------------ > Agent 1 15 ads > Agent 2 3 ads
This looks like two different results... a SQL query returns _one_ resultset. Try this: SELECT CompanyName,AgentName,count(*) as ads FROM table GROUP BY CompanyName,AgentName ORDER BY CompanyName,AgentName; <URL: http://www.mysql.com/doc/en/SELECT.html > The output could be something like this: ComanyName AgentName ads --------------------------------- Realty Company 1 Agent 1 10 Realty Company 1 Agent 2 8 Realty Company 1 Agent 3 20 Realty Company 2 Agent 1 15 Realty Company 2 Agent 2 3 If you really want separate result tables for each company, you would probably need to use some programming language. -- Roger --------------------------------------------------------------------- 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
