使用MSSQL GROUP BY列太多

Question

How can I use Group By to my LOCATION column on my query which is having I think an aggregate things?

SELECT a.[PlateNo]
,a.[TrxDate] as DATES
,a.[Location] ,a.[account]
,a.[TrxTime]
,a.[Msg]
,b.company FROM [Mark_Fast].[dbo].[Alarm] a
inner join [Mark_Fast].[dbo].[account] b 
on a.[account] = b.senderno or a.[account] = b.sim1 
where a.trxdate BETWEEN '09/10/2015' AND '09/10/2015' and Msg LIKE '%geo%' 
and (a.PlateNo = 'BCY536') ORDER BY Location desc

The sample output is:

PlateNo  Dates    Location   account   TrxTime          Msg             company
123     9/9/1999   Loc 1      321      02:39:00  Geozone Exit Alert!    Transpartner Trucking Services
123     9/9/1999   Loc 1      321      02:39:00  Geozone Exit Alert!    Transpartner Trucking Services
123     9/9/1999   Loc 1      321      02:31:00  Geozone Entry Alert!   Transpartner Trucking Services
123     9/9/1999   Loc 3      321      02:32:00  Geozone Exit Alert!    Transpartner Trucking Services
123     9/9/1999   Loc 3      321      02:33:00  Geozone Exit Alert!    Transpartner Trucking Services
123     9/9/1999   Loc 1      321      02:34:00  Geozone Entry Alert!   Transpartner Trucking Services
123     9/9/1999   Loc 2      321      02:35:00  Geozone Exit Alert!    Transpartner Trucking Services
123     9/9/1999   Loc 2      321      02:37:00  Geozone Entry Alert!   Transpartner Trucking Services

I want the output to be Group by Location but I don't know how to execute it in my query. What am I trying to achieve is to have an output that looks like this:

PlateNo  Dates    Location   account   TrxTime          Msg             company
        123     9/9/1999   Loc 1      321      02:39:00  Geozone Exit Alert!    Transpartner Trucking Services
        123     9/9/1999   Loc 2      321      02:39:00  Geozone Exit Alert!    Transpartner Trucking Services
        123     9/9/1999   Loc 3      321      02:31:00  Geozone Exit Alert! Transpartner Trucking Services

Answer 1

If you want to see aggregate of accounts group by location you'll have to add following to your query.

GROUP BY a.Location HAVING sum(a.accounts)

If you can give sample of expected output with all column heads you may get a better answer.

For latest TrxTime you need to add following lines to your query

GROUP BY a.Location HAVING max(a.TrxTime)