Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
676
questions
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 ...
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 ...
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 ...
- 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
...
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 ...
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 ...
- 1
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)...
- 121
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 ...
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
...
- 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 = [...
- 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,
...
- 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 (...
- 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 ...
- 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:...
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) ...
- 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:
+----+-------------+-------...
- 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 ...
- 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
- 133
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": {
&...
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 ...
- 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 ...
- 113
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/...
- 1
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 ...
- 113
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 ...
- 111
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 ...
- 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 ...
- 1
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,
...
-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;
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 ...
- 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
) ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 11
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 ...
- 137
-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:...
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
...
- 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
---------- --------- --------- ------ --------- -----...
- 123
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' ...
- 3
-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
...
- 13
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=...
- 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-...
- 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
...
- 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 ...
- 225
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-...
- 23
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;
...
- 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 ...
- 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 |...
- 1