Hello Everyone and thanks for your help in advance. I am in need of creating a query that retrieves several fields and a total count for each distinct Name in the database. I tried:

SELECT MLS_Agent_Name AS AgentName, MLS_AGENT_ID AS AgentID, COUNT(*) AS ListingCount
FROM tblListings
GROUP BY MLS_Agent_Name, MLS_AGENT_ID With RollUp
GO

In this case, I am trying to retrieve both the agent name, agent ID, and a total count of occurrences for each agent. However, the way the group by clause works in this case, it retrieves two set of names for each distinct name, obviously due to the second grouping. However, when I remove the second column in the Group By parameter, the query throws an error. I am really not sure if I am going ab out this the wrong way or even if what I want can be done in one query. Any help on this topic would be greatly appreciated. Thanks.

Try this one
SELECT MLS_Agent_Name AS AgentName, COUNT(*) AS ListingCount
FROM tblListings
GROUP BY MLS_Agent_Name

if you want some other result then pl explain.



Enjoy!
Subodh Kumar
Phoneytunes.com

Just wanted to add, when you do aggregation using Group By you have to include all columns (excluding columns in aggregate functions like Count,Sum etc) in Select Statement as Group By parameters.

RollUp is used when you want to summarize you selection.