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

Question

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

Navigation

#1 by (3 votes)
#2 by (3 votes)

3

I wonder if there is a way to group records in the search order. Let's say I have a table of two Local (mapped place), Time (Date and Time) fields. 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, three lines of result would appear:

Recife   - 3
SãoPaulo - 1
Recife   - 1

Is there a possibility?

sql group-by

asked by anonymous 15.08.2016 / 21:31

2 answers

My javascript function does not call action controller Select too slow, how to improve?

score 3 · Answer 1

If it is, SQL Server, we can group using sintexe:

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

To use the GROUP BY command, an aggregate function is required, ie, speak to SQL Server, as it must aggregate the fields that are the same as the cluster.

Example:

Suppose a table as below:

NowIwanttogroupthecolumnaliq_ipi,wewilldo:

selectaliq_ipi,count(ncm)fromNcmGroupByaliq_ipi

Result:

The aliq_ipi column has been grouped by counting the ncm column, ie all ncm that have the value of the aliq_ipi equal column 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 your physical table's data, that 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