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

Asked

Viewed 654 times

3

I wonder if there is a way to group records in the order in which the survey is presented. Let’s say I have a table of two fields Location (mapped place), Time (Date and Time). Place was filled in the same place then in three different times, then I have new posts and back in 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 arise three lines of result:

Recife   - 3
SãoPaulo - 1
Recife   - 1

Is there such a possibility?

sql group-by
  • 1

    Which database? Could be a Cursor?

  • I don’t see how. Unless you can distinguish between these records to group them into different groups. Type: write to the database in which reading these records were obtained.

  • 1

    See if the answers below answer your question and choose one as correct.

  • ---> Thank you all for your attention! ---> The database is SQL Server (but if someone knows a standard code can be adapted), I already do by the cursor wanted to know if there was a way for the grouping to be performed this way without the need to create the temporary table... A way to increase the performance of my research (this example I gave is very simple, but the amount of data and interspersed records is much higher).

2 answers

3

If so, SQL Server, we can group using 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 to provide an aggregation function, that is, speak for SQL Server, as it should aggregate the fields that are equal when grouping.

Example:

Suppose a table as below:

inserir a descrição da imagem aqui

Now I want to group the column aliq_ipi, we’ll do:

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

inserir a descrição da imagem aqui

Upshot:

The column was grouped aliq_ipi counting the column ncm, that is, all the ncm which have the value of the column aliq_ipi equal are grouped.

In your case we can do:

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

3

One way to do this would be a course, with temporary tables to store the data from your physical table, it 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

inserir a descrição da imagem aqui

Browser other questions tagged sql group-by

You are not signed in. Login or sign up in order to post.