All Questions
Tagged with group-by postgresql
148 questions
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 ...
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
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
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
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
55
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
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
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
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
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
0
votes
0
answers
58
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 * ...
1
vote
0
answers
71
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
2
votes
2
answers
492
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 ...
- 263
0
votes
1
answer
2k
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 ...
- 197
0
votes
1
answer
462
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
3
votes
1
answer
227
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
-2
votes
1
answer
64
views
Why is GROUP BY clause requiring the value?
When we write 'group by' in any relational database the syntax always requires the values. Can't we make the syntax a little simple by not mandatory to provide group by values?
Any columns mentioned ...
- 99
2
votes
1
answer
225
views
Aggregate query for pairs of arrays of events
db<>fiddle for all of the data and queries below
I have a table events with the following structure:
create table events (
correlation_id char(26) not null,
user_id bigint,
...
- 175
0
votes
1
answer
318
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
6
votes
2
answers
27k
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 ...
3
votes
0
answers
621
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)...
- 131
1
vote
1
answer
706
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
187
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 ...
- 111
-1
votes
1
answer
108
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
1
vote
2
answers
3k
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;
...
- 133
0
votes
1
answer
802
views
How can I group multiple records as a single .csv string line?
I have a relation where a User has multiple dogs (as many as 15) but each dog is contained in a single row in the table, and they all have a userId in common.
For example, Table `dogs`:
| User | ...
- 231
3
votes
1
answer
12k
views
How to use ORDER BY and LIMIT inside JSON_AGG
I have a query which returns a desired output.
SELECT
shop,
JSON_AGG(item_history.* ORDER BY created_date DESC) as data
FROM item_history
GROUP BY
shop;
Results:
[
{
"shop&...
- 33
1
vote
2
answers
3k
views
GROUP BY, but use only one row per user
I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems.
I have the table cities with columns user_id and city....
- 113
2
votes
2
answers
680
views
Speeding up PostgreSQL GroupAggregate
I currently have two tables. UserRoll (447.633 rows) and UserRollResult (4.476.330 rows). First table contains data of who rolled, where it was rolled, when it was rolled, etc as well as a identifying ...
- 79
0
votes
1
answer
5k
views
How to conditionally create JSON array in postgres with group by clause?
I am using Postgresql 13.0 and have a table category as follows:
cat_id | cat_name | piece | weight | is_tag_stock
--------------------------------------------
4 | cat1 | 20 | 10 | ...
- 135
0
votes
1
answer
4k
views
Posgres database requires a group by clause. (column \"candidates.id\" must appear in the GROUP BY clause or be used in an aggregate function",)
I have 2 tables, agents and candidates. The candidates table has a foreign key "agent_id". I want to get all agents and aggregate the candidate counts on each agent object; the different ...
0
votes
0
answers
144
views
How can I update query to use GROUP BY instead of DISTINCT while still preserving ORDER BY?
Trying to make this query more performant (basically I need to have distinct speech_ids from the speech_candidate table while at the same time counting the number of duplicate speech_ids for each of ...
1
vote
2
answers
344
views
Unexpected behaviour for GROUP BY query
CREATE TABLE public.temp (
id integer,
a integer,
b integer,
x integer
);
select x, a, b
from temp
group by id, a, b;
ends up with expected error:
ERROR: column "temp.x&...
- 153
1
vote
2
answers
83
views
Group by with a break on date
I am trying to group data on homogenous periods of time (not sure this is clear).
Here is a simplified example of my data:
CREATE TABLE workhours_over_time (
worker varchar,
...
- 452
0
votes
1
answer
148
views
Select records and related entity count
Given the following select that will count related entities for every record
SELECT MAIN.*, SUB.app_count
FROM my_table_view MAIN
LEFT JOIN (SELECT MAIN2.p_key,
count(...
- 303
0
votes
1
answer
628
views
Selecting non-aggregate column on 3 table inner join
I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres:
donations --(belong to)--> campaigns --(belong to)--> organisations
Each table has an id ...
- 621
3
votes
1
answer
2k
views
Count percentage of column with GROUP BY
Consider the following query:
SELECT
country
, count(id) AS "count"
FROM
lead
WHERE
date_part('year', lead.since) = date_part('year', CURRENT_DATE)
GROUP BY "country"
ORDER BY "count" DESC
;
...
- 143
0
votes
0
answers
146
views
Using rank in postgresql
With this query in postgresql
WITH ExtractedPosition AS (
SELECT P.rider_id,
P.start_date,
P.day_index,
P.start_hour,
P.end_hour,
extract(...
1
vote
1
answer
581
views
Return previous running total when value is null in a time series
This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).
All of this is being done on a PostgreSQL ...
- 139
1
vote
1
answer
656
views
Generate multiple running totals with GROUP BY day
I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
- 139
0
votes
2
answers
307
views
Aggregating data by group and time gaps
I've got a postgresql+postgis database filled with GPS data:
CREATE TABLE "my_table" (
"id" serial NOT NULL PRIMARY KEY,
"gps_id" text NOT NULL,
"lat" numeric NOT NULL,
"lon" ...
- 145
0
votes
1
answer
2k
views
PostgreSQL error: "subquery uses ungrouped column"
I have a query with subqueries that use only elements from the GROUP BY clause:
SELECT
DATE(cm.created_at) as "date",
COUNT(1) as total_message,
room_id,
(SELECT ...
- 111
0
votes
0
answers
41
views
MAX with group by by 2 columns
I have a query
create table film_actor (
actor_id integer,
film_id integer);
create table film (
film_id integer,
title varchar);
create table actor (
actor_id integer,
...
1
vote
1
answer
436
views
Avoid Sort on Indexed Group By caused by DATE_TRUNC
Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order ...
- 35
0
votes
1
answer
447
views
How to create month ranges without using a specific date in Postgresql
What's the best way to create month ranges without using specific dates?
For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month ...
- 43
0
votes
1
answer
408
views
postgresql cumulative counts in date range
I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active.
I have a table like this:
group_id | id | type | start_date ...
0
votes
3
answers
81
views
How to group rows while filtering out values per each group?
Imagine you have the following setup:
CREATE TABLE namechanges (
id text,
new_name text,
change_date timestamp
);
INSERT INTO namechanges VALUES
('x', 'alice', '2020-03-01'),
('y', 'Bob T.'...
- 113
1
vote
1
answer
260
views
Postgresql group by for many columns
I have such query:
SELECT "Movie"."title",
"Movie"."id",
"Movie"."publicationDate",
"Movie"."authorID",
"Author"."photoURL" as "authorPhoto",
concat("Author"."...
- 123
0
votes
3
answers
188
views
Postgres fetch exact value when satisfied all conditions
Here are the table structure and sample data.
CREATE TABLE public.product (
id serial NOT NULL,
opid int4 NULL,
opvalue int4 NULL,
info ...
- 135