Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [aggregate-filter]

The FILTER clause is SQL standard syntax to filter rows passed to an aggregate function.

Filter by
Sorted by
Tagged with
1 vote
1 answer
312 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 ...
user avatar
1 vote
2 answers
31 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 +----+-...
user avatar
0 votes
1 answer
19 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, ...
user avatar
1 vote
1 answer
69 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 ...
user avatar
  • 311
7 votes
2 answers
6k 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 = '...
user avatar
  • 1,103
2 votes
1 answer
109 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 "...
user avatar
7 votes
1 answer
17k 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, ...
user avatar
  • 71
6 votes
1 answer
11k 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 | ...
user avatar
0 votes
3 answers
117 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 | -...
user avatar
  • 101
2 votes
1 answer
123 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 ...
user avatar
  • 21
90 votes
1 answer
91k 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 ...
user avatar
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 ...
user avatar
1 vote
1 answer
2k 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 "...
user avatar
  • 115
12 votes
2 answers
39k 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,...
user avatar
  • 377
2 votes
1 answer
3k 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 | ...
user avatar