Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
752 questions
0
votes
2
answers
32
views
Disable MySQL ONLY_FULL_GROUP_BY while logging violations
We have attempted to upgrade a legacy database from MySQL 5.6 to 5.7 in the hopes of bringing it up to current, but quickly experienced queries that failed with this is incompatible with sql_mode=...
- 195
0
votes
0
answers
30
views
Postgres: How to select rows, grouping by rows with same column field into one row, with pagination
I've a table, with fields like
id
batchId
senderId
recipientId
uuid1
uuid
uuid5
uuid7
uuid2
uuid
uuid5
uuid8
uuid3
uuid2
uuid6
uuid9
I need to select rows with pagination, but handling rows with ...
-1
votes
1
answer
24
views
concatenate data from column year when GROUP BY on Column Amount (MySQL)
I have a table from which I generate a view Grouping By Year and summing the amounts, and I wonder if I could somehow include a "string sum" or "string concatenation" of the ...
- 139
0
votes
0
answers
46
views
Mysql SUM inside Left Join return wrong value when using with rollup function
Good Afternoon.
I have some problem/missunderstood with rollup function when using LEFT Join statement. the sum from rollup function at the end is not like my expected. please see the sample table ...
- 29
2
votes
1
answer
18
views
JSON_CONTAINS in HAVING clause adds extra closing brace to JSON_OBJECTAGG
I have a table with data we've collected from various sources. It's got the item name, the source and the source's value.
The table looks like the following:
id
name
source
value
1
abc
web
1
2
abc
...
- 121
0
votes
3
answers
38
views
Select object from first table, who doesn`t have true in second
I have table with objects
uuid
name
98665e5a-e1cb-4bf7-93fa-a8d13983f358
object
and another table with information about enabled this object and another object together
uuid_from_table_upper
...
1
vote
1
answer
31
views
update with group column in the same table
I try to assign random value in ref column.
You can see my try in https://dbfiddle.uk/949fv4di
Result should be :
ref
prod
23
prodA
23
prodA
12
prodB
12
prodB
12
prodB
23
prodA
7
prodC
23
...
- 113
2
votes
1
answer
115
views
How can I get all results concatenated from a column group by UID?
I need to build a SQL query, for MySQL 5.7, if it is possible, to "GROUP BY" uid and title and concat the permissions column and separate with a |? I am saying GROUP BY but I do not know if ...
- 1,888
0
votes
1
answer
86
views
How to select 3 rows, using offset & limit, but merge rows with same ID
SELECT message."id" AS "id"
, message."transactionId" AS "transactionId"
, "ids"
, message"createdAt" AS "createdAt&...
- 11
4
votes
1
answer
105
views
What defines a "column" in a GROUP BY statement
A question came up about an error in a Stack Exchange Data Explorer query which I tried to diagnose. The error reads:
Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS"...
- 190
1
vote
2
answers
83
views
Insert missing rows after counting by group
This is similar to Postgresql group by and count() problem, missing row with 0 result but different in genesis and details.
I have a table of temperatures in Celsius and dates and I use ...
- 291
0
votes
0
answers
35
views
How to mark a calculated query column as total:sum in Microsoft Access?
How can I ensure for Design View that the "total" line of this calculated column shows "sum"?
It seems to change after running the query and saving it.
What starts as:
TotalRevenue:...
- 1
0
votes
0
answers
61
views
How is the ! used in Microsoft Access queries?
The "bang" or exclamation point is a logical operator.
Just to follow up on a previous question, is it problematic to use "!" with Microsoft Access queries to specify tables and ...
- 1
0
votes
1
answer
155
views
In Postgres, duplicate rows on a column are not detected by select group by, but unique index built is failing with duplicate values
I have a table with users and column username. I am trying to build an index on the users#username column but it is failing with an error for a duplicate value. The value detected is an Arabic ...
- 109
0
votes
1
answer
54
views
Distinct based on window function (sort curiosity or wrong problem approach)
A lot of values are stored in values. The data is in a hierarchical order, so there could be a probe, on which some measurements have been performed. Many points have been archived with strain and ...
- 1
0
votes
0
answers
60
views
SQL Query group by to find EffectiveFrom and EffectiveTo Dates for repeating key value pair
I have a table with a snapshot of repeated data for a key and value pair with record_timestamps. The dataset can also contain late-arriving historical record_timestamps as well.
I want to generate an ...
0
votes
1
answer
125
views
Why does Redshift ignore length when grouping by VARCHAR of only spaces?
Imagine I have a column name that is n rows and the nth entry is equal to REPEAT(' ', n). For SELECT DISTINCT name FROM table I would expect to receive n rows because each name is unique by definition....
0
votes
0
answers
35
views
Speedup wordcloud select with group by in mysql
I am currently working on a project for my studies where a wordcloud is created for the NYT. Therefore I am scraping all the articles from the NYT from their API, and then store dates of the article, ...
- 1
1
vote
2
answers
93
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 13
0
votes
1
answer
65
views
Group by a column and select the group id on condition that applies to all the rows in it
I have the below table
CREATE TABLE result(
id INTEGER NOT NULL
,cid INTEGER NOT NULL
,aid INTEGER NOT NULL
,agid INTEGER NOT NULL
,ft INTEGER NOT NULL
,ct INTEGER ...
- 197
1
vote
0
answers
34
views
Group each set of consecutive seats within a row_id in Snowflake
I want to group row_id and seat_number as follows so I can group by row_id and group_id (desired column) to pick the minimum seat number as start_seat and the maximum seat number as end_seat:
row_id ...
- 11
1
vote
1
answer
91
views
Limit results of complex query to most frequent values in given attribute
Say that you have the following tables for a multi store platform
CREATE TABLE orders (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
store_id BIGINT NOT NULL,
ordered_at TIMESTAMPTZ NOT ...
- 113
0
votes
1
answer
23
views
I am getting an infinite fetch duration when adding a similar third left join
I am using MySQL on Windows 10.
The query below should result in three groups of data sets that are different only in the date range. It works for any two date ranges but when I try to run all three ...
- 1
0
votes
1
answer
82
views
Postgresql group by multiple columns
Let's say I have this table:
sellerid
buyerid
price
abc
xyz
100.00
abc
xyz
500.00
ikj
abc
200.00
ikj
abc
300.00
And I need to get the sum of price for each id (sellerid and buyerid):
id
total
...
- 3
2
votes
3
answers
636
views
Conditional selection in a GROUP BY clause
I am an SQL beginner and ran into the following problem. Consider a table with data on countries, cities within that country and their population:
CREATE TABLE cities (
country VARCHAR(20),
...
- 121
0
votes
1
answer
40
views
Why use $group / group_by instead of multiple requests?
Let's suppose that there are 100 types of dogs scattered across the documents of my collection. If I need to group by types of dogs and then compute some aggregate statistic about each type of dog, ...
0
votes
1
answer
42
views
Adding subtotals using GROUPING SETS
For a data table I would like to create the following table:
store
department
gender
workers_count
A
Light
Male
10
A
Light
Female
5
A
Plumbing
Male
20
A
Plumbing
Female
0
2
35
B
Food
Male
12
...
- 121
1
vote
1
answer
118
views
Select first row of a particular group with one attribute selected as 0 while all other rows should be selected as is
I have a requirement where I need to select first row of a particular group and for all other groups I need to select the row as is in the same query.
Here is my table
CREATE TABLE mytable(
afid ...
- 197
0
votes
1
answer
47
views
GROUP BY with multiple counts
I'm querying an Invoice table in a MySQL 8.0.28 database that is a child to the Customer table (FK = CustomerId) and I want to be able to get multiple counts based on different criteria over the last ...
- 103
-1
votes
1
answer
65
views
Query for grouping by comparing sum of value for group, stopping when quantity is reached
Trying to calculate values for a FIFO report. Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group, including taking ...
- 281
0
votes
1
answer
148
views
Query for grouping by comparing sum of value for group
Is there a way using SQL to group data such that you're comparing the sum of a particular value against different values for each group?
For example, consider the following schema:
Table: ITEM
Columns:...
- 281
0
votes
0
answers
78
views
Modifying an SQL query for All Combinations of Two Variables
I am working with Netezza SQL.
I have the following table ("my_table"):
id year var1 var3 date_1
1 1 2017 1 1 NA
2 1 2018 0 1 NA
3 1 2019 1 1 NA
4 2 2017 ...
- 143
1
vote
0
answers
43
views
How to have another column showing first date from partiton by clause?
select distinct ea.FIN
, dem.patient_name
, dem.dob
, age_in_years(e.beg_effective_dt_tm::date, dem.dob) as Age_at_Encounter
, cv.display as Facility
, cv2.display as Nurse_unit
, cv3.display as Loc
, ...
- 21
-1
votes
1
answer
49
views
how to GROUP BY so that not all results are grouped
When I use my query to select all data from the table I have a problem with the custnumber field. If custnumber is returned within the same month then it should group the results but only then.
This ...
- 1
0
votes
1
answer
99
views
Set variable on multiple case within each group
Say I have below table
cis
aid
cid
apid
ntime
buid
flg
jid
30
1208
229067737
1026
9
DDDD400C
0
0
30
1209
229067737
1026
11
DDDD400C
0
0
30
0
229067737
1026
12
DDDD400C
1
100
30
1210
229067737
...
- 197
2
votes
1
answer
112
views
Same Variable Changing its Min/Max Values within the Same Group?
I am working with Netezza SQL.
I have the following table ("my_table"):
gender country favorite_color disease height weight id
1 m mexico blue n 193.6547 62....
- 143
1
vote
0
answers
40
views
Calculating Percentages within Percentiles
I am working with Netezza SQL.
I have the following table:
CREATE TABLE MY_TABLE (
id INT,
gender VARCHAR(1),
disease VARCHAR(1),
income DECIMAL(10, 3)
);
INSERT INTO MY_TABLE (id, ...
- 143
0
votes
1
answer
18
views
coalesce works only once
I have a table with redundant values in the first two columns:
sqlite> select * from amino where "lebensmittel" = 'Walnüsse';
Lebensmittel Protein pro 100g Histidin Isoleucin Leucin ...
- 359
-1
votes
2
answers
155
views
What properties should the column in GROUP BY Clause have in SQL?
I've found group by to be very confusing topic in SQL. The database vendor doesn't matter to me but I'm using MySQL.
Till now, I've done lots of reading, and I'm still unable to determine what the ...
- 101
0
votes
1
answer
319
views
How does MySQL handles GROUP BY?
I'm on Windows 10, MySQL Workbench. I'm on MySQL 8.0.
Here's the dataset. northwind.sql.
https://pastebin.com/bMgjXvfT
Objective:
Write a query to get the order ID, customer's name, grand total of ...
- 159
0
votes
0
answers
31
views
Why is GROUP BY companyName and GROUP BY customerId producing same results? [duplicate]
I'm using the northwind.sql.
This is the schema diagram for the northwind.sql.
https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf
Objective:
Write a query to get ...
- 159
3
votes
1
answer
3k
views
Not getting the concept of COUNT with GROUP BY?
This is not my job, neither my homework. I'm just self-learnin from Prof. Donatus course on udemy and these are the problems he used in his course.
Objective 1:
Your logistics manager wants to know ...
- 159
0
votes
1
answer
50
views
How to get only last relation sorted by datetime
I have users and their payments (ok/fail) and need to select all users that have last payment failed (by date).
Mariadb 10+.
That's schema and how i've tried to solve this, but definitely wrong: http:/...
- 103
-2
votes
1
answer
276
views
Daily average from a 15mins interval datetime column o
I want to convert this 15 minutes interval data into daily average such that in the output file, I should have only two columns 'Date' and 'Daily_Avg_Value'.
This is my first post here - really wish ...
- 9
2
votes
2
answers
678
views
How to sum two different queries and group them?
amount
from_client
to_client
transaction_type
200
NA
Jacques
tsa
400
John
NA
tsb
800
John
NA
tsb
800
NA
Jacques
tsa
100
Jacques
NA
tsb
I would like to get the sum of Jacques and John ...
- 33
2
votes
1
answer
137
views
mysql count of a column based on date and type
I have the following table optimizations
select items_type,
created_at,
count_optimized_items
from optimizations
where shop_id=126
and count_optimized_items!=0
items_type
created_at
...
- 23
2
votes
3
answers
61
views
IF(GROUPING(id)=1, "Total", name) returns the same wrong "name" across all rows, except for the total case
I'm using MySQL 8.0 and performing select query with totals and sub-totals, hence it's about with rollup statement.
The problem is that the expression IF(GROUPING(id)=1, "Total revenue", ...
- 21
1
vote
2
answers
567
views
Identify “duplicates” within a table by using GROUP BY column1 OR column2
Generally speaking, I want to group by one column OR by another one:
SELECT count(*)
FROM foo
GROUP BY column1 OR column2
In particular, I want to identify duplicates within a given table, below an ...
- 111
0
votes
0
answers
24
views
creating max_variables within subqueries
I have SQL query:
select a.merge_key, a.var1,
a.var2,
a.var3,
a.name_of_person,
a.name_of_automobile,
a.price_of_automobile, b.merge_key as m,
b.var1 as v,
b.var5,
b.var6,
b.var7,
from first_table ...
- 143
4
votes
2
answers
3k
views
PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
There's a technique in Oracle SQL that can be used to simplify aggregation queries:
Aggregate on a particular column, but get information from a different column, using a simple calculated column in ...
- 1,497