Questions tagged [aggregate-filter]
The FILTER clause is SQL standard syntax to filter rows passed to an aggregate function.
21
questions
0
votes
0
answers
12
views
Find document with regular expressions in collections related to populate in mongoose
The aim is to filter a collection of documents (Requests) based on the document field from another collection (Patients, Clients, Doctors, and Services), of which there are several references in the ...
- 101
1
vote
1
answer
264
views
Query optimisation - how to reference lateral join table to avoid duplicate lookup
First time posting here but long time reader.
I'm fairly new in my role working on optimising sql queries. Most are generated from KNEX.js leading to some peculiar artifacts that might make sense in ...
- 13
0
votes
1
answer
2k
views
How do I query only rows where multiple instances with the same ID exist with Postgres?
I have a table where I want to gather some statistics, but only for items that are repeated two or more times. To simply, here is how the table looks like:
CREATE TABLE test (
id bigint,
...
- 125
0
votes
1
answer
1k
views
Return type mismatch in function declared
I am working in supabase, where I want to make a function with the help of SQL queries to obtain the last record of a user.
As it is a query that would not look entirely good using the traditional ...
- 101
0
votes
2
answers
645
views
How to count rows matching certain conditions within each group?
I have more columns in my table vb10_cartridgedata but the following two columns are my columns of interest: NestID and FailCode. NestID can only contain numbers from 1 to 16. I am trying to count ...
- 21
1
vote
1
answer
58
views
Improving postgreSQL 13.3 performance on a materialised view?
I found one of our APIs using this query in our materialised view which generates aggregated report on asset inventory (PostgreSQL 13.3) and is very slow on a table with 1.2 million rows
SELECT ...
- 111
1
vote
1
answer
2k
views
CASE statement not returning expected results [closed]
First time using Postgres and building a query with a CASE statement. I am trying to end up with a collection of data whereby one of the columns in the data (status) qualifies the row based on ...
- 119
1
vote
2
answers
395
views
Build a select query that includes aggregated values from a different table
I have two tables in Postgres: polls and votes.
The first one polls is designed to contain the poll-related data. Every poll is aimed to have only two possible responses — option_a and option_b
+----+-...
0
votes
1
answer
22
views
Add aggregated value filtered by a condition to existing query
I have this table to collect data from IoT device:
CREATE TABLE public.conferimenti
(
id bigint NOT NULL DEFAULT nextval('conferimenti_id_seq'::regclass),
codicestazione smallint NOT NULL,
...
1
vote
1
answer
87
views
Getting both Having and Not Having in same row
I have a query which I get the customers that have more than 1 orders
However, I would like to get the number of customer that has only 1 orders as well and divide them like 1 orders / more than 1 ...
- 321
9
votes
2
answers
8k
views
Accumulate values into an array with Postgres
Currently I have this query:
select
sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
sum(case when my_table.property_type_mapped = '...
- 1,163
2
votes
1
answer
206
views
GROUP BY with subqueries for a Group?
Here's a simplified recreation of what I have:
CREATE TABLE "DocumentStore" (
"ID" int NOT NULL,
"Content" jsonb NOT NULL,
CONSTRAINT documentstore_pk PRIMARY KEY ("ID")
);
INSERT INTO "...
- 123
9
votes
1
answer
23k
views
Consolidate multiple rows into single row
In PostgreSQL 9.5, I have a table named reports:
CREATE TABLE public.reports (
id BIGSERIAL PRIMARY KEY,
id_station character(11) NOT NULL,
date date NOT NULL,
element character(4) NOT NULL,
...
- 91
6
votes
1
answer
15k
views
How to count multiple values of a column as one group? [duplicate]
I have a table main with the following rows:
id | name | rank
----+--------+------
1 | Ali | a
2 | Sami | b
3 | Khan | c
4 | Kamran | d
5 | Imran | e
6 | Asad | a
7 | ...
0
votes
3
answers
303
views
Using start and end event logs to create a table/view containing spans between the times of each log
To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following:
-------------------------------------
| user | event | team | timestamp |
-...
- 101
2
votes
1
answer
441
views
Join table into different columns
I have two tables (simplified).
table1:
- id (serial)
table2:
- table1_id (integer)
- value (text)
- type (integer)
I want to join these two tables, but depending on the type, I want ...
- 21
119
votes
1
answer
126k
views
Postgres Count with different condition on the same query
EDIT Postgres 9.3
I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2
The current query is working fine which looks like this:
Basically it is a 3 table inner ...
- 1,389
4
votes
1
answer
5k
views
Better way to select from nested tables in PostgreSQL
I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
- 143
1
vote
1
answer
3k
views
Postgres Nested WHEN Aggregate Function
In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by "...
- 115
13
votes
2
answers
43k
views
Return counts for multiple ranges in a single SELECT statement
I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3,...
- 397
2
votes
1
answer
4k
views
Split two rows into two columns
I have the following table:
id | name | action | count
------------------------------
1 | Value1 | 0 | 27
1 | Value1 | 1 | 49
2 | Value2 | 0 | 7
2 | Value2 | 1 | ...
- 173