SQL Query and group by

Hi,

I was wondering how to group the report data under group headings like we get by using - "Step3 - Select a column to group by", when using SQL query instead of report creation wizard.

We are using SDP version 7509 with MySQL DB

My SQL query looks something like:

SELECT aaov.NAME "Site",workstation.WORKSTATIONNAME "Workstation",resource.ASSETTAG "Resource Tag",resource.SERIALNO "Serial Number",LONGTODATE(resource.ACQUISITIONDATE) "Acquisition Date",product.COMPONENTNAME "Product" FROM SystemInfo workstation LEFT JOIN Resource_Fields resFields ON workstation.WORKSTATIONID=resFields.RESOURCEID LEFT JOIN Workstation_Fields wsfields ON workstation.WORKSTATIONID=wsfields.WORKSTATIONID LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID WHERE state.DISPLAYSTATE = 'Data Center' ORDER BY 1

and I am trying to group these by "Site" (aaov.NAME)

If I try to use "GROUP BY aaov.NAME" or "GROUP BY 1", I only get first row from each site and nothing else.

Reason behind creating an SQL query instead of using one of the wizards was that we wanted to get "Resource Tag" (resource.ASSETTAG, which is incorrectly noted as ASSETAG in the resource schema) which is not available in "Step1: Select Columns to Display"

Please let me know if you would require further information.

Regards,
M. Woodwal