Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
732
questions
0
votes
1
answer
29
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
15
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
...
- 111
1
vote
1
answer
28
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
28
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
38
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 ...
- 279
0
votes
1
answer
44
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:...
- 279
0
votes
0
answers
26
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
0
votes
0
answers
16
views
Finding Out All Possible Combinations of Years Per ID per Group
I am working with Netezza SQL.
I have the following table ("my_table") which has information on university students over a period of years (2010-2015), the major they were enrolled in during ...
- 143
0
votes
0
answers
39
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
, ...
- 1
-1
votes
1
answer
40
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
21
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
102
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
23
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
14
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 ...
- 239
-1
votes
2
answers
34
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
147
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 ...
- 145
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 ...
- 145
3
votes
1
answer
821
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 ...
- 145
0
votes
1
answer
37
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
90
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
67
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
26
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
52
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
0
votes
0
answers
34
views
Get value of column B by having column A duplicate IDs
I have table of policies and i need to get policies that do not have "Policy" status.
Every policy have the same POLICY_DETAILS_ID but different POLICY_STATUS_DEBUG.
The query should say: ...
1
vote
2
answers
142
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
17
views
In Oracle SQL, How do I select an aggregate column from the result of a group by function? [duplicate]
SELECT *
FROM Clients c
JOIN Info i
ON i.id = c.info_id
JOIN (
SELECT
site_owner_id,
SUM(p.amount_paid)
FROM Site s
JOIN SecurityArrangement sa
ON sa.site_id = ...
- 1
0
votes
0
answers
23
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
2
votes
2
answers
1k
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,437
0
votes
1
answer
18
views
MySQL Group By ordering with legacy database structure
I'm dealing with a legacy database with about 2 million rows. It's used for tracking order information. It's basically a transactional log of order entry and all edits, cancellations, etc., to that ...
- 11
0
votes
2
answers
62
views
Selecting last value to be entered each month
I am looking to pull a closing balance from the database for each month.
I have tried
SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
WHERE MAX(DateTime)
Group By ...
- 43
0
votes
0
answers
47
views
Create a column containing group size in postgres
Given the following table:
create table t(x integer);
insert into t(x) values
(1), (1), (2), (2), (2);
I'd like to create an additional column which contains the size of the groups, the expected ...
- 276
0
votes
0
answers
32
views
Select columns from joined tables, group or distinct by a single column, and order by another column
Here's essentially what I'm trying to do:
SELECT
deals.*,
retailers.title AS retailer_title,
ST_Distance(retailers.lonlat, 'SRID=4326;POINT(-118.4104684 34.1030032)' :: geography, 15 * ...
0
votes
0
answers
45
views
Overlap on Max/Min dates
I have data in a table that has gotten cut up with multiple start and end dates. This is due to an error that has happened a few times in the last year. Apparently causing the system to not see the ...
- 11
2
votes
1
answer
37
views
How to get the most recent result sets when grouping key and date are in separate tables
Good evening! I'm having some difficulty. I'm trying to obtain the most recent row from a few tables using SQL. I can do this using a loop in code, but it requires a lot of round trips to the database ...
- 121
0
votes
1
answer
75
views
Products multiple variants
My query looks like:
select p.id, p.name, vo.value, v.name
from products p
join products_variant_options pvo on pvo.product_id = p.id
join variant_options vo on vo.id = pvo.variant_option_id
join ...
- 123
1
vote
1
answer
32
views
Grouping results belonging to the same product number into the same record
I have a very long query that I have shortened and anonymized so the problem becomes clearer.
Here is an approximation of my query:
SELECT subquery1.productNumber,
subquery1.anotherinfo,
...
- 11
1
vote
0
answers
53
views
Group by Rollup not rolling up - Except in 1 case
I want to perform a group by rollup calculation (I know varchar as ID is bad - it's out of my control)
The query is correct insofar as each individual department but the sub-departments are not being ...
- 11
0
votes
2
answers
295
views
Making a query to sum up all records of a subgroup excluding a specific instance
I'm struggling to make a query that looks at a series of finance records for an individual and gets the amount owing on one record and also (separately) the amount owing on any previous records
Here ...
- 450
0
votes
1
answer
128
views
Why would a group by return multiple groups with the same value?
In the query
select pricing_date,count(*)
from prices
where pricing_date>='2022-12-01'
group by pricing_date;
I receive results with multiple pricing_date groups of the same date:
+-------------...
- 193
2
votes
2
answers
189
views
Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?
I've got a table with ~2 billions rows of data, and I would like to make another table with some aggregates. It looks like PostgreSQL uses temporary disk space for these queries. I can create the ...
- 161
0
votes
1
answer
729
views
Use skip scan index to efficiently select unique permutations of columns in Postgres
I've already read this excellent answer for emulating skip scan index to retrieve the distinct values for a single column. This is blazingly fast.
Now what I'm trying to do is to retrieve unique ...
- 165
0
votes
1
answer
112
views
Improving Performance of Group By sum Query to get payment data based on total Employees
I have a query to show payment data paid by grouped total Employee (1,2,3) (their size [how much number of users are in total Employee)) and the total amount.
But My query took 30-35 seconds
result ...
- 105
2
votes
1
answer
493
views
Do multiple MYSQL json_arrayagg(s) preserve order?
select media_id,
json_arrayagg(artist_id) as artist_ids,
json_arrayagg(role) as role_ids
from x_media__artist xma group by media_id
Hi guys, do artist_ids and role_ids preserve their order as ...
- 31
0
votes
0
answers
474
views
Auto increment id after group by some values using sequelize
I have table already have id and it's unique.
id* status other column ...... amount
1 1 100
2 1 1000
3 1 ...
1
vote
1
answer
214
views
How to GROUP BY boolean when im already grouping by time frames 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time frame and grouped by bolean "R" or "L". I ...
- 29
0
votes
1
answer
216
views
[Resolved]: How to define the query for group by when using jsonb_query_path functions in the SELECT
I am trying to define the following GROUP BY query and need some help. I am using PostgreSQL DB table with a jsonb column
select
count(*),
policynumber
from
(
select
'My ...
- 103
0
votes
1
answer
166
views
How to group by week/month/year and time frame 8hr/11hr/14hr/17hr/20hr in MySQL
I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users.
Example of tables ...
- 29
-2
votes
1
answer
22
views
Extracting data from two tables
I have( for example) a table called A and a table called B.
Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME.
I want to find out how ...
- 1
2
votes
1
answer
91
views
'Group by' on indexed TEXT columns not working
I'm creating the following table in MySQL:
CREATE TABLE group_by_test (
txt1 TEXT,
txt2 TEXT,
txt3 TEXT,
val INTEGER,
INDEX (txt1(10), txt2(10))
);
I then insert a bunch of rows, ...
- 155
3
votes
1
answer
143
views
PgSql: how could I achive a group by that checks any match on any of two columns?
I am trying to implement a NMS filter for labels in a Database.
NMS is a filter that removes redundant squares. On the image provided we can see there is only one tree, but there are three labels ...
- 51