Most voted "group-by" questions
GROUP BY is a standard SQL relational database command for collapse result lines that share a common field value on a single line. Aggregation functions can be performed in other group fields, such as SUM() or AVG(), to gather related data into a single value. also used in other languages like Linq.
Learn more…125 questions
Sort by count of
-
20
votes4
answers850
viewsReturn all equal items from different groups
------------------- - TABLE - ------------------- ID | GRUPO | OBJETO ---|-------|------- 1 | 1 | 1 2 | 1 | 2 | | 3 | 2 | 1 4 | 2 | 2 | | 5 | 3 | 1 6 | 3 | 2 7 | 3 | 3 | | 5 | 4 | 1 6 | 4 | 3 | | 7…
-
11
votes2
answers14331
viewsHow does Groupby work on LINQ?
I’m having trouble understanding the operator Groupby on LINQ.
-
8
votes3
answers13600
viewsHow to get the value of a column corresponding to the maximum of another column?
I have a table with columns codigo, data and valor. I would like to get, for each code, the value corresponding to the most recent date. In what ways can this be done? And if there is more than one,…
-
7
votes1
answer729
viewsError using Group by in a Mysql VIEW
I created a VIEW to return in a single query the main data I need. CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW v_historicoProcesso AS SELECT p.protocolo AS…
-
7
votes2
answers1132
viewsSelect too slow, how to improve?
I have a chart of artisans with about 90,000 records. I created the SQL below to return me the total of artisans in each of the regions that they are registered. Although it works, it is slow,…
-
6
votes2
answers228
viewsWhat is the most efficient way to implement Groupby in Javascript?
I’m trying to implement a GroupBy with these parameters function GroupBy(keySelector, elementSelector, comparer) { // keySelector = function(e) { return e.ID } // elementSelector = function(e) {…
-
6
votes4
answers34297
viewsTake the highest value per category by also loading the ID
The table is in the format below ID | CATEGORIA | QUANTIDADE 1 | A | 23 2 | A | 12 3 | A | 11 1 | B | 15 2 | B | 10 Wanted a query to return to me the following table ID | CATEGORIA | QUANTIDADE 1 |…
-
6
votes3
answers839
viewshow to select zero Count
have these tables (example) Municipio (id, nome) 1, Santos 2, Sao Paulo 3, Rio de Janeiro 4, Florianopolis 5, Porto Alegre 6, Natal Cliente (id, nomecliente, cidade) 1, Joao, 1 2, Pedro, 3 3, Maria,…
-
6
votes4
answers829
viewsBest approach is to filter data in the database or application?
Suppose we have a table 'Vendas' with the following columns 'estado', 'cidade', 'vendedor', 'valor'. 'estado' and the 'cidade' would be where the sale was held and 'vendedor' would be what made this…
-
5
votes2
answers1709
viewsHow do I implement the Groupby method in Typescript?
I have an array: interface IFoo { IDCidade: number; Nome: string } var arr = [ { IDCidade: 10, Nome: "Foo" }, { IDCidade: 10, Nome: "Bar" }, { IDCidade: 20, Nome: "Foo" }, { IDCidade: 20, Nome:…
-
5
votes2
answers1104
viewsProblem with group by
I have the following query: SELECT funcionario.*, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa FROM funcionario,emprego,empresa WHERE empresa.id = emprego.id_empresa AND…
-
4
votes1
answer235
viewsGrab the latest message from a conversation (GROUP BY)
I have the following query: SELECT cp_mensagem.mensagem, cp_mensagem.dh_envio, cp_projeto.id as id_projeto, cp_projeto.nome as nome_projeto, cp_pessoa.id as id_freelancer, cp_pessoa.nome as…
-
4
votes1
answer1076
viewsField SUM of two MSSQL Sub-consultations
I need a field derived from two sub-consultations that gives the sum of values based on a field. Follow example: SQL: SELECT CodCliente ,DataVenda ,Vendedor ,Valor ,DataPagamento ,TotalCliente…
-
4
votes3
answers351
viewsGroup by days interval using Linq
How can I use interval grouping with Linq? var dados = new[] { new { Id = 0, dias=100, preco= 25, Nome="etc"}, new { Id = 1, dias=40, preco= 50, Nome="etc1"}, new { Id = 2, dias=50, preco= 55,…
-
4
votes3
answers438
viewsOver with Group by
I have a query that I use to return me as an extract, accumulating the values record by record: SELECT TOP (100) PERCENT Mes, Credito, Debito, Sum(Credito - Debito) over (ORDER BY Emp_id, Mes) AS…
-
4
votes1
answer784
viewsSelect the first row of each group in Mysql
Suppose I have such a table: item | group -------+-------- item a | group x item b | group y item c | group y item d | group x item e | group z And that I want to select a single item to represent…
-
4
votes2
answers97
viewsSearch for lower values of one field according to values of another
I have this data entered in my table. mysql> SELECT * FROM db_cotacao; +----+------------+-------+------------+--------+---------+ | id | data | valor | validade | idforn | idativo |…
-
4
votes3
answers11645
viewsUsing Groupby in Pandas dataframe
good afternoon. I don’t have much skill with Python, I’m having some doubts. Anyone who can help me, I thank you. I opened my csv file in python as follows: import pandas as pd caminhoArquivo =…
-
3
votes1
answer5211
viewsSQL Group query with more than one distinct attribute
I have an application that generates approximately 25000 records per day in BD. This will generate a large amount of id’s per day in the user’s BD. According to the user’s business rule, only failed…
-
3
votes2
answers556
viewsGroup Postgresql query results
I have the regular table with the query. status |curso |matricula| --------- |------|---------| Completo |Inglês|001 | Cursando |Mat |002 | Reprovado |Esp |003 | Completo |Inglês|004 | Cursando |Esp…
-
3
votes1
answer105
viewsGroup on an equal footing
I need to group the result when there are equal records, regardless of the order, whenever the same colors are chosen - group the colors and the users. I need to produce an output like the one…
-
3
votes1
answer79
viewsCluster difficulty and Where clause in Mysql
I need to search the database for the last 4 most recent records, the column being exr_exa_id is in agreement with the informed Ids, and exr_exa_id may not be duplicated. I tried to group in some…
-
3
votes2
answers654
viewsHow to list the data of an SQL query grouped in order of data presentation
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…
-
3
votes1
answer625
viewsJavascript - Soma e Group By em Array
I would like a tip/help. I’m working with a number of files JSON, and applying filters to them as the user advances in the system. So far so good, I managed to do it quietly using the .filter(). My…
-
3
votes1
answer335
viewsGROUP BY does not work in Mysql 5.7
Locally I have Mysql 5.6 installed, and this SELECT works smoothly: SELECT dia FROM indicacao GROUP BY YEAR(dia), MONTH(dia) ORDER BY dia DESC Already on the client server, with Mysql 5.7 this same…
-
3
votes1
answer142
viewsList last logged in users (GROUP BY AND ORDER BY)
I have two tables (user and log), and would like to list users ordering by last logged in. I am using the GROUP BY to join the logs (since a user may own more than one). The problem occurs when I…
-
3
votes2
answers254
viewsGroup By different results
Hi, I have a question if anyone can help me on SQL. I know the Group By clause, as far as I know it groups similar data. But I understand that it should group but in the script below it has the…
-
3
votes1
answer701
viewsHow to use a COUNT with condition?
I have a table quarto, wanted to return the amount of rooms you have on each floor (the room has an attribute andar), but I don’t know how to get him back from every floor SELECT andar, COUNT(*…
-
3
votes1
answer323
viewsGroup items in application table Asp.net web MVC
How can I group items into a table? I am bringing these items from the database, I would like to group by the code of the same, for example, if there is repeated code 1 I would like to group it into…
-
2
votes2
answers379
viewsError group by Oracle
When I try to make this group by in oracle it returns me message saying that this is not a group by expression SELECT produto.cd_produto, produto.ds_produto, est_pro.cd_estoque, estoque.ds_estoque,…
-
2
votes2
answers50
viewsdoubt with query in sql
I have a table with 5 fields: id name office salary date ...and wanted to make a query that returns the name of the people with the highest salary, of each position, and I have for example 3…
-
2
votes1
answer2986
viewsGroup two fields in the same table with mysql
I’m trying to put together a query, where I can print out the broker’s name and the sales percentage for it. When grouped by name1, the field name_broker2 does not group and vice versa, when grouped…
-
2
votes2
answers805
viewsGroup by by two fields ordered by a third
I have the following data: |--------------------------------------------------------| |USER | ID | DEVICEID | DATE | |--------------------------------------------------------| |7 | 14450 |…
-
2
votes1
answer38
viewsError when grouping table field
I need to group the data of a SELECT across the countryside a.afo_vch_NmrCtf, because the data is being returned as follows: When I use the GROUP BY the following error is returned : Msg 8120, Level…
-
2
votes1
answer94
viewsProblems in Mysql query
I’m having difficulty in performing a query between 4 tables, being them. States > Municipalities > Deaths > Population The query is returning the value of the population with a larger…
-
2
votes1
answer66
views2 Groupby in a Mysql query
In Mysql I have a login table with the field day and User ID: 28-01-2019 - a 28-01-2019 - a 29-01-2019 - a 29-01-2019 - b 29-01-2019 - b 30-01-2019 - a 30-01-2019 - b 30-01-2019 - c What I need is…
-
2
votes1
answer64
viewsGroup result through a conditional
I wonder if in Mysql or any other Bank you have the possibility to use GROUP BY with a conditional. I will try to explain I have a status column that can have the value true and false, would like to…
-
2
votes2
answers49
viewsHow do I list all fields from one table plus a random value from another table?
I’m having a problem making the relationship with two tables, tb_adm_anuncios and tb_adm_anuncio_banner. For each ad I have 4 banners, and to list these ads I need: **Anúncio:** -Nome do Anúncio…
-
2
votes2
answers4640
viewsLEFT JOIN WITH GROUP BY
I am using PHP and Mysql, I have two tables in the database: POSTAGEM id ---- 1 3 13 CURTIDA id | id_usuario | id_postagem ----------------------------- 1 | 1 | 1 7 | 12 | 1 What I can’t do is this:…
-
2
votes1
answer3058
viewsSQL distinct with sum
Hello, I have a problem making a sum in a query. Example and query results: select DISTINCT t.ID, t.nomeuc as "Nome UC", t.tipoturno as "Tipo Turno", a.num_presencas as "Número de Presenças" from…
-
2
votes2
answers211
viewshow can I get the last message from my`msg`field with`GROUP BY`
good evening, I wonder how can I bring the last message from my group by, because I’m not getting it. is always bringing the first occurrence of the consultation and I always need to take the last…
-
2
votes2
answers116
viewsDifficulty returning the result in a single query without duplicating
I am trying to assemble a query (MYSQL) to return me all the clients that a particular seller can view. The rules to show the list of customers for this seller are: - Customers with contracts…
-
2
votes1
answer371
views -
2
votes1
answer58
viewsDoubt about SQL - Group by (beginner)
I have two tables, one with the names of doctors and the other with the classifications of the appointments and I want to list the doctors and the quantities of appointments based on the…
-
2
votes1
answer60
viewsApply a function varying between categories in R
I need to calculate the elasticity - price of demand of a specific product (percentage variation of the quantity demanded divided by the percentage variation of the price). For that I made the…
-
1
votes1
answer3334
viewsUsing Group By in SAS Guide
I’m having trouble using the command GROUP BY, in the SAS. By definition, SAS only accepts the GROUP BY if used in the SELECT a summary function. So far, ok. Running the Proc below, the result comes…
-
1
votes1
answer1710
viewsGROUP BY and SUM at Crystal Report
I need to do something relatively simple with Crystal Report but I’m not getting it. I have a report like this: OP | CLIENTE | PREÇO 1 João 10 1 João 20 1 João 30 2 Marcelo 15 3 Rodrigo 35 3 Rodrigo…
-
1
votes1
answer177
viewsSQL: Can I have clauses in Groupby that don’t appear in Select?
I know I can’t have clauses in select that don’t appear in Group By. But is it possible otherwise? For example: Select B From Table Group By A,B
-
1
votes1
answer96
viewsSql query return the same name with different date
My table Acidente is more or less like this: Vitima - DataRegistro Pedro - 10/02/2015 Pedro - 20/03/2015 Maria - 01/05/2015 Maria - 01/05/2015 I need to make a query (Sql) that returns the records…
-
1
votes1
answer321
viewsFrom CTE in SQL to relational algebra
How to pass a CTE in SQL for relational algebra, for example: with publicacao_cte (nome,nr_publicacoes) AS ( select candidato.nome, Count(1) as nr_publicacoes from candidato, cv, ficha_inscricao,…