使用MSSQL GROUP BY列太多

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

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)

try to use aggregate function MAX():

SELECT a.[PlateNo], a.[TrxDate] as DATES, a.[Location], a.[account], MAX(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') GROUP BY a.[PlateNo],a.[TrxDate],a.[Location],a.[account],a.[Msg],b.company

Grouping only by Location will not because other columns have different data.

Therefore you will have to add more columns to show the records uniquely. GROUP BY [Location], [TrxDate], [TrxTime], [Msg]