Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

Filter by
Sorted by
Tagged with
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, ...
Bear Bile Farming is Torture's user avatar
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 ...
gbox's user avatar
  • 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 ...
Himanshuman's user avatar
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 ...
snappymcsnap's user avatar
-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 ...
Sarov's user avatar
  • 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:...
Sarov's user avatar
  • 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 ...
stats_noob's user avatar
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 ...
stats_noob's user avatar
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 , ...
Michelle's user avatar
-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 ...
James's user avatar
  • 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 ...
Himanshuman's user avatar
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....
stats_noob's user avatar
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, ...
stats_noob's user avatar
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 ...
ceving's user avatar
  • 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 ...
zeeshanseikh's user avatar
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 ...
achhainsan's user avatar
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 ...
achhainsan's user avatar
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 ...
achhainsan's user avatar
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:/...
trogwar's user avatar
  • 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 ...
Raj's user avatar
  • 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 ...
COPEGUEST's user avatar
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 ...
Apoorv dev's user avatar
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", ...
Skill's user avatar
  • 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: ...
Sabir Masih's user avatar
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 ...
lavalade's user avatar
  • 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 = ...
Kun.tito's user avatar
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 ...
stats_noob's user avatar
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 ...
User1974's user avatar
  • 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 ...
K Zavin's user avatar
  • 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 ...
Paulmcf1987's user avatar
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 ...
baxx's user avatar
  • 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 * ...
JohnDoe1999's user avatar
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 ...
daveyjones88's user avatar
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 ...
jeremysawesome's user avatar
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 ...
kicaj's user avatar
  • 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, ...
Guest's user avatar
  • 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 ...
N0ug4t's user avatar
  • 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 ...
akc42's user avatar
  • 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: +-------------...
enharmonic's user avatar
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 ...
Michal Charemza's user avatar
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 ...
hunter's user avatar
  • 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 ...
Abdul Rehman's user avatar
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 ...
Joe Yan's user avatar
  • 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 ...
user19304185's user avatar
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 ...
Rollo's user avatar
  • 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 ...
adbdkb's user avatar
  • 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 ...
Rollo's user avatar
  • 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 ...
Gal Mor's user avatar
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, ...
aioobe's user avatar
  • 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 ...
J Pablo F's user avatar

1
2 3 4 5
15