Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

Filter by
Sorted by
Tagged with
0 votes
0 answers
6 views

MYSQL -> SELECT, JOIN, GROUP BY to UPDATE

I get the data I need with the following select statement: SELECT tk.actiondate, tk.completedate, tk.closedate, MAX(com.date) AS realcomplete, MAX(clo.date) AS realclose FROM ticket tk LEFT JOIN ...
user avatar
0 votes
0 answers
42 views

PostgreSQL- Grouping results without using GROUP BY

I'm trying to understand the logic behind the group by and the possible alternatives to it. That's why, I've been trying to solve a puzzle where I try to make the same as with a group by clause but ...
user avatar
0 votes
1 answer
17 views

Postgresql - how to use bool_and with group by

I have the following schema, that represent collection of messages, where each message can have multiple items (at least 1). Every item belongs to a message. Items can change over time. CREATE TABLE ...
user avatar
  • 147
3 votes
2 answers
63 views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
3 votes
1 answer
130 views

How to transpose values from one column into columns with a value from a different column?

I have a database with the following structure: Date role type duration 2022-04-16 Nurse Food preparation 45 2022-04-17 Nurse Cleaning 30 2022-04-17 Volunteer Cleaning 20 2022-04-17 Nurse Food ...
user avatar
0 votes
2 answers
50 views

How to manipulate the output of a GROUP BY CUBE query changing the NULL values generated for the groups?

I wrote a query to sum salaries grouped by Country and Gender. I don't want the rows to show NULL or empty values for the totals, instead I want it to show Total and Grand Total to better describe the ...
user avatar
2 votes
0 answers
39 views

How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?

What would be the best way to aggregate across hierarchies? I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) - folder Column Type Description folder_id (pk)...
user avatar
0 votes
0 answers
25 views

Optimizing a view query that uses pivot aggregation - Oracle

I have a table with columns as - | ID | cat_1 | cat_2 | type | value | |----|-------|-------|------|-------| |1 | a |b | t1 | 4 | |2 | b |d | t2 | 5 | |3 | b |e ...
user avatar
1 vote
1 answer
60 views

Optimize performance of nested GROUP BY

I have a query on the table random_numbers with a GROUP BY nested inside another GROUP BY: SELECT a, min(x_max) AS x_minmax FROM ( SELECT a, b, max(x) AS x_max ...
user avatar
  • 31
0 votes
1 answer
30 views

GROUP BY date column and then group by custom daytime

So I have to group a table according to the date and then the timeday when a product arrived, the timedays would be: morning = [5, 6, 7 , 8, 9] mid_morning = [10, 11] midday = [12, 13, 14] evening = [...
user avatar
  • 49
1 vote
1 answer
18 views

query relational tag data, grouping by a different metric

Suppose I have the following schema: CREATE TABLE tag_group ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, ); CREATE TABLE tag ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, ...
user avatar
  • 111
0 votes
1 answer
75 views

Using OPENJSON, can't get results to GROUP BY

I'm passing a JSON string to a stored procedure in order to insert warehouse inventory. Sometimes, there will be multiple line items with the same product, going to the same shelf, on the same pallet (...
user avatar
  • 3
0 votes
1 answer
50 views

Creating a parent table from child relationships

I have a child relationship table as CREATE TABLE ItemRelationships ( Item1 int(11) unsigned NOT NULL, Item2 int(11) unsigned NOT NULL, INDEX(Item2), FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON ...
user avatar
  • 4,169
0 votes
1 answer
37 views

Possible to GROUP BY across different columns in mariadb/mysql

I have a table full of events similar to this: `date` | start_time | end_time | length -------------------------------------------- 2022-01-10 | 07:00:00 | 16:00:00 | 9 2022-01-11 | 07:00:...
user avatar
0 votes
1 answer
35 views

UPDATE a table by self JOIN and GROUP BY

I have a table of team players in which the PlayerID is missing in some rows. CREATE TABLE TeamPlayers ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, TeamID int(11) unsigned NOT NULL, PlayerID int(11) ...
user avatar
  • 4,169
0 votes
0 answers
30 views

Grouping null and not null together

I'm trying to GROUP BY fingerprint & email, managing nulls emails values. I must aggregate ids of null emails by fingerprint as they are probably the same user. So this: +----+-------------+-------...
user avatar
  • 101
0 votes
0 answers
33 views

HQL aggregate based on other column

Currently working with Spark data frames using HQL syntax (which slightly differs from SQL). I'm trying to aggregate a table by id and want to select the feature row by the most recent date (they may ...
user avatar
  • 101
0 votes
1 answer
271 views

How to GROUP by month & year with an epoch timestamp in AWS Redshift

Assuming a very simple schema as pictured below. (unfortunately the column is actually named timestamp) How do I simply get a count on records grouped by YEAR & MONTH
user avatar
0 votes
0 answers
119 views

GroupBy with count array in MongoDB

I have a collection and object where this object has a list of objects as following: { "_id": { "$oid": "615617f5b0cd1765b7ba21c1" }, "response": { &...
user avatar
1 vote
1 answer
60 views

Query to filter only groupings having different values

I need a simple query, but couldn't find the way to do it: I have a table of services purchased by customers, where different customers may purchase the same service (trivially), and each customer may ...
user avatar
  • 331
0 votes
1 answer
54 views

Having and Group By clause

I came across this question yesterday. I am looking for an explanation for just one point. Which of the following statements are TRUE about an SQL query? P : An SQL query can contain a HAVING clause ...
user avatar
0 votes
2 answers
23 views

Count dated entries between dated milestones from 2 tables

I have a set of milestones with random dates on table 1 and a set of items with random dates on table 2 (see below). Table 1 Milestone MilestoneDate A 01/01/2021 B 03/15/2021 C 05/27/2021 D 09/09/...
user avatar
1 vote
2 answers
55 views

SQL having clause

I was studying about the having clause when I came across this website. The last two lines of this article have made me puzzled. Similar to the WHERE clause, the HAVING clause requires that the ...
user avatar
1 vote
1 answer
73 views

How do I split an array between GROUP BY groups?

I'm trying to create a query which will put some rows in multiple groups. I'm doing this by generating an array that contains both categories I want to group by. Here's my query so far (slightly ...
user avatar
0 votes
0 answers
40 views

How to get the functionality of sqlplus' "BREAK ON" in Postgres?

Is there a way to separate a query that has a group by attribute in Postgres in a fashion similar to sqlplus? Assuming I have branches of my company in every country, is there a way to have a separate ...
user avatar
  • 11
0 votes
0 answers
96 views

aggregation with group by, inner join and nested conditions in MongoDB

First of all I am sorry if this is a basic question, I am new to queries in MongoDB. Well, what I need is to find the latest registers for a worker in my WorkerLocationContext document and the latest ...
user avatar
0 votes
1 answer
12 views

MySQL How can I create a dynamic result of numbers grouped by an "upto" value with a count

I'm looking to group a set of numbers by an "upto" value and show a count of how many results are up to this value omitting any zeros e.g. data could be 38600, 46526, 35813, 26629, 36127, ...
user avatar
-3 votes
1 answer
30 views

I tried running this script, but i kept getting not a group by error [duplicate]

select a.*, b.Start_Date, b.End_date, sum(b.cost) as total_Cost from Ess.TZeewrld a left join Ess.Wrldest b on a.account_number, = b.account_number, group by b.Start_Date, b.End_date;
user avatar
0 votes
2 answers
38 views

Finding the minimum of a column depending the value of a second column

I have (on sqlite) a table with the following structure : id tile_id creation_year creation_day 1 1 2020 150 2 1 2020 132 3 2 2021 13 4 2 2020 15 5 3 2021 110 6 3 2021 18 7 3 2021 150 How can ...
user avatar
  • 103
3 votes
1 answer
71 views

How do I aggregate related rows when there's no group key?

I'm using MariaDB 10.6 and have a table: CREATE TABLE velocities ( `state` int(8) NOT NULL, `timestamp` datetime NOT NULL, `velocity` decimal(5,2) NOT NULL, `name` varchar(15) NOT NULL ) ...
user avatar
  • 133
0 votes
1 answer
51 views

mySQL Query with a LIMIT on a row value

I am trying to replace a lot of queries like this with a single query. SELECT * FROM y WHERE x = a ORDER BY timestamp LIMIT 50 SELECT * FROM y WHERE x = b ORDER by timestamp LIMIT 50 SELECT * FROM y ...
user avatar
0 votes
2 answers
145 views

LIMIT the number of rows in GROUP BY and GROUP_CONCAT with ORDER

In this sqlfiddle, CREATE TABLE t1 ( id int(11) unsigned NOT NULL, val int(11) unsigned NOT NULL, rank mediumint(7) unsigned, INDEX(id), INDEX(rank), PRIMARY KEY(id,val) ) ENGINE=InnoDB DEFAULT ...
user avatar
  • 4,169
0 votes
2 answers
72 views

What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?

I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like: WHERE varcharColumn <> '' and varcharColumn2 = '2' and ...
user avatar
  • 45
2 votes
2 answers
91 views

Group by x, get other fields too (comparing options and performance)

I'm a novice, trying to find the best way to sum up my data. Oracle 19c. I have a WORKORDER table that has work orders (ISTASK=0) and tasks (ISTASK=1). Tasks are children of work orders. Work orders ...
user avatar
  • 1,373
3 votes
1 answer
39 views

How to group data but taking rows only in a specific order (example should be clearer ;) )

I have something i need to do and i don't know how to do it :) I have the following data : Art Type 2215 Z 2215 C 2215 M 2216 C 2216 The result i want to have Art Type 2215 M 2216 C Those ...
user avatar
  • 33
-4 votes
1 answer
72 views

I want to perform max and sum aggregate on same column for different where clause in SQL server [closed]

I have a patient with 2 rows that have same admit and discharge dates and their admit flag is 1 and los is 14, 15 and other patient with 3 rows with different admit and discharge dates that have admit ...
user avatar
3 votes
4 answers
410 views

How to use GROUP BY in a way concatenates data in one column, but filters for specific data in another

I am running SQL Server 2014 I have a table that looks like this: ID | Name | AddressType | Address | Features ======================================================== 1 | Bob | Home ...
user avatar
-1 votes
1 answer
53 views

how to select date time step by 60 second from first value to last value in table

I want to add 60 seconds to the date value from the first date and return the data if it exists in that interval like this datetime value 2021-05-02 12:20:1 5 2021-05-02 12:20:15 8 2021-05-02 12:...
user avatar
0 votes
1 answer
79 views

SELECTing n rows for each GROUP with WHERE condition

I SELECT n rows for each ArticleID from TagMap table SELECT a.ArticleID,a.TagID FROM TagMap a LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID GROUP BY a.ArticleID,a.TagID ...
user avatar
  • 4,169
2 votes
2 answers
46 views

T-SQL Group and sum record set based on value in a given column

Take the following example: SELECT * FROM [Orders] WHERE DATE = '2021-09-10' Date FirstName LastName Adults Item Description ---------- --------- --------- ------ --------- -----...
user avatar
0 votes
2 answers
56 views

Finding groups which have two values in another column

Given the following sample rows: MTYPE RNO VAL S 1 V1 S 1 V2 S 1 V3 R 1 V4 S 2 V5 S 2 V6 R 3 V7 R 3 V8 How can I list groups of RNO which has both 'R' ...
user avatar
-2 votes
1 answer
64 views

Group by on three tables as one master table and two detail tables

I.tblOwnerDetails Ownerid ownername 1 Abc 2 Xyz 3 Pqr II.tblListMaster Listid Listname 1 List1 2 List2 III.tblListDetails Detailid Listid Ownerid 1 1 1 2 1 2 3 2 1 4 3 1 IV.tblTagMaster ...
user avatar
1 vote
0 answers
42 views

JOINing two mapping tables

I have two mapping tables as CREATE TABLE ProjectDevelopers ( DeveloperID int(11) unsigned NOT NULL, ProjectID int(11) unsigned NOT NULL, INDEX(ProjectID), PRIMARY KEY(DeveloperID,ProjectID) ) ENGINE=...
user avatar
  • 4,169
0 votes
1 answer
74 views

Slow group by execution after update to MySQL 8.0.26

I am running a larger DB with tables > 200M datasets on a dedicated Ubuntu 20.04 machine. Since a few days queries that use group by are extremly slow. I suspect that this is new since the last apt-...
user avatar
  • 283
-1 votes
1 answer
47 views

db query error: pq: column "systemevents.receivedat" must appear in the GROUP BY clause or be used in an aggregate function

When running the following query I receive an error: SELECT $__timeGroupAlias(receivedat,$__interval), SUBSTRING(message, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}') AS the_address, COUNT (message) AS ip ...
user avatar
  • 1
0 votes
2 answers
422 views

Why does GROUP BY on table A cause "using temporary; using filesort" on table B? [closed]

Given the following contrived example code: SELECT A.author_name , COUNT(T.title) AS num_titles FROM authors A JOIN titles T ON T.authorFK = A.authorPK GROUP BY ...
user avatar
2 votes
3 answers
708 views

Get a count of consecutive dates

I want to get a list of dates where there are consecutive dates and it's corresponding count. For example, if the I have the following data set Date 2021-07-28 2021-07-27 2021-07-26 2021-07-25 2021-07-...
user avatar
1 vote
2 answers
270 views

Postgresql group by aliased jsonb field

I'm trying to group by the result of a jsonb operation on an aliased field, but getting an error I would not expect. The following work as expected: select jsonb_build_object('x', 1) as a group by a; ...
user avatar
  • 123
0 votes
1 answer
35 views

Select Top item from group [duplicate]

I have a simple table ID Product Version Price Notes 1 Pizza 1 10.00 It is good 2 Pizza 2 11.00 The best yet 3 Soda 1 3:00 4 Hot dog 1 8.00 20% meat 5 Soda 2 3.00 Rebranded I want to get out ...
user avatar
  • 1
0 votes
1 answer
43 views

Sum A Column Using Unique Groups Of Time Periods

I have data that looks like this: name |sides|start |end ----------+-----+----------+---------- STR00248 |2 |2021-01-04| STR00250 |1 |2021-01-04| STR00246 |...
user avatar

1
2 3 4 5
14