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]