CODEWITHSUNDEEP
sqlsql-server
FSou1
FSou1

Reputation: 1941

Question about aggregate functions with GROUP BY SQL Server

I have for example 10-15 fields in table different types (varchar, nvarchar, int, float, datetime e.t.c.) and i need to make GROUP BY, so. What function of aggregate i have to use on all of this fields? MAX or something else? Is it important?

SELECT
    intFacilityRTOPID,
    MAX(ObjectSystem_r_Equipment) ObjectSystem_r_Equipment,
    MAX(ObjectBuilding_r_Equipment) ObjectBuilding_r_Equipment,
    MAX(intenum_EquipmentTypeID) intenum_EquipmentTypeID,
    MAX(correctionDate) correctionDate
FROM [RTOPv4History].[dbo].[FacilityRTOP]
WHERE cast(correctionDate as bigint) <= @correctionDate
GROUP BY intFacilityRTOPID

Upvotes: 0

Views: 219

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Sounds like you might not understand what Group By does. Group By establishes a set of "bins" or "buckets", defined by the values of the group By columns or expressions, that will control the output of the query. i.e., the result rows of the query will be constrained to unique combinations of the values defined by the group by columns and/or expressions, and all data in each row is constrained from the subset of the original table that mach that "definition"..

Any columns in the eoutput that are not exactly the same as one of the group by column/expressions must use one of the many aggregate functions to specify and/or calculate what value to generate for that column. The value generated will be taken from actual table column values from only those rows in the original table that match the group By column/expression. So if you use MAX(), you get the biggest of that subset of the values, if you use AVG() you get the average, etc...

If you really don't want to do any aggregation, then consider just using the Distinct keyword....

 SELECT Distinct intFacilityRTOPID, 
     ObjectSystem_r_Equipment ObjectSystem_r_Equipment,
     ObjectBuilding_r_Equipment ObjectBuilding_r_Equipment, 
     intenum_EquipmentTypeID intenum_EquipmentTypeID,
     correctionDate correctionDate 
 FROM [RTOPv4History].[dbo].[FacilityRTOP] 
 WHERE cast(correctionDate as bigint) <= @correctionDate

Upvotes: 2

Sklivvz
Sklivvz

Reputation: 31133

If the complementary column are all equal, you may want to 

SELECT * FROM (
  SELECT DISTINCT(intFacilityRTOPID) 
  FROM [RTOPv4History].[dbo].[FacilityRTOP]
  WHERE cast(correctionDate as bigint) <= @correctionDate
) a CROSS JOIN (
  SELECT TOP(1) 
    ObjectSystem_r_Equipment,
    ObjectBuilding_r_Equipment,
    intenum_EquipmentTypeID,
    correctionDate
  FROM [RTOPv4History].[dbo].[FacilityRTOP]
  WHERE cast(correctionDate as bigint) <= @correctionDate
) b

which could perform better depending on the size of the table. Caveat: I haven't tried if this works and I am writing by memory :-)

Upvotes: 0

Related Questions