Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

Filter by
Sorted by
Tagged with
1 vote
1 answer
31 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 ...
Charlie Clark's user avatar
0 votes
0 answers
8 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:...
Nick's user avatar
  • 1
0 votes
0 answers
25 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 ...
Nick's user avatar
  • 1
0 votes
1 answer
36 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 ...
p.matsinopoulos's user avatar
0 votes
1 answer
41 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 ...
lrahlff's user avatar
0 votes
0 answers
33 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 ...
user137279's user avatar
0 votes
1 answer
25 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....
Tanner Clary's user avatar
0 votes
0 answers
30 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, ...
Ynfiniti's user avatar
0 votes
2 answers
69 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 ...
nyoatype's user avatar
0 votes
0 answers
13 views

how to use the group by clause [duplicate]

I have a query usign the clause group by Such query returns 2 rows, that are 2 groups I'd like to extract ONE record belonging to the first group and ONE record belonging to the second group How do I ...
user1632812's user avatar
0 votes
1 answer
44 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 ...
Himanshuman's user avatar
1 vote
0 answers
19 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 ...
kapashh's user avatar
  • 11
1 vote
1 answer
79 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 ...
nbon's user avatar
  • 113
0 votes
1 answer
21 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 ...
Mathly's user avatar
  • 1
0 votes
1 answer
68 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 ...
Exrotz's user avatar
  • 3
2 votes
3 answers
595 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), ...
dzejkob's user avatar
  • 121
0 votes
1 answer
33 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
25 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
  • 121
1 vote
1 answer
64 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
29 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
42 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
96 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
53 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
40 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
48 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
50 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
106 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
28 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
15 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
  • 317
-1 votes
2 answers
84 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
246 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
2k 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
47 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
191 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
296 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
49 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
58 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", ...
lostsoul's user avatar
1 vote
2 answers
356 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
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
4 votes
2 answers
2k 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,457
0 votes
1 answer
20 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
86 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
36 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
81 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
38 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
93 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
59 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

1
2 3 4 5
15