How to list the data in an SQL query grouped by order of presentation of the data

Question

How to list the data in an SQL query grouped by order of presentation of the data

I would like to know if there is a way to group records by the order of presentation of the search. Let's say I have a table of two fields Local (mapped place), Time (date and time). Place was filled in the same place then in three different times, then I have new places and back to the same place:

Recife   - 2016-08-15 13:59:44
Recife   - 2016-08-15 14:30:44
Recife   - 2016-08-15 15:59:44
SãoPaulo - 2016-08-15 19:00:00
Recife   - 2016-08-15 20:59:44

In this grouping would appear three result Lines:

Recife   - 3
SãoPaulo - 1
Recife   - 1

Is there such a possibility?

3

sql group-by

Author: Fabricio, 2016-08-15

Source

2 answers

score 3 · Answer 1

If so, SQL Server, we can group using the syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

To use the command GROUP BY it is necessary provider an aggregation function, that is, speak to SQL Server, as it should aggregate the fields that are equal when grouping.

Example:

Suppose a table as below:

Now I want to group The Column aliq_ipi, we will do:

select aliq_ipi, count(ncm) from Ncm Group By aliq_ipi

Result:

Foi grouped the column aliq_ipi by counting the column ncm, that is, all ncm that have the value of the column aliq_ipi equal are grouped.

For your case we can do:

SELECT cidade, count(data) FROM nomedatabela GROUP BY cidade

score 3 · Answer 2

One way to do this would be a course, with temporary tables to store the data from your physical table, this would look like this;

declare @municipios table
(
  municipio varchar(100),
  data datetime
)

declare @municipios_teste table
(
  municipio varchar(100),
  total int
)

insert into @municipios values 
('Recife','2016-08-15 13:59:44'),
('Recife','2016-08-15 14:30:44'),
('Recife','2016-08-15 15:59:44'),
('SãoPaulo','2016-08-15 19:00:00'),
('Recife','2016-08-15 20:59:44')


declare @municipio varchar(100) ,@municipioold varchar(100) = '', @data datetime, @contador int = 0

DECLARE _cursor CURSOR FOR   
select * from @municipios

OPEN _cursor  
FETCH NEXT FROM _cursor INTO @municipio  , @data

WHILE @@FETCH_STATUS = 0  
BEGIN  

    if(@contador = 0 or @municipioold = @municipio)
    begin 
         if(@contador = 0)
            set @contador = 1;
         else 
            set @contador += 1;

         set @municipioold = @municipio;

         delete @municipios_teste 
         where municipio = @municipio
         and total = @contador - 1;

         insert into @municipios_teste
         SELECT @municipio, @contador
    end 
    else 
    begin
        set @municipioold = @municipio;
        set @contador = 1;

        insert into @municipios_teste
        SELECT @municipio, @contador
    end 
    FETCH NEXT FROM _cursor INTO @municipio, @data  
    END  

CLOSE _cursor  
DEALLOCATE _cursor  


select * from @municipios_teste