Linked Questions
26 questions linked to/from Return counts for multiple ranges in a single SELECT statement
6
votes
1
answer
14k
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 | ...
38
votes
3
answers
43k
views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
- 4,320
10
votes
4
answers
18k
views
How to count boolean values in PostgreSQL?
How to count, how may true and false for the field public in postgresql user table
i have tried this query
select
sum(case when false then 1 else 0 end) as false,
sum(case when true then 1 else 0 ...
- 453
2
votes
2
answers
2k
views
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]
I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT:
SELECT
customs.id AS custom_id, customs.custom_name AS ...
8
votes
1
answer
21k
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,
...
- 81
2
votes
1
answer
10k
views
How to use values from table column as field names in SELECT query?
Scenario:
Field names of Table1 named as testtable
id,name,size,width,height
Field names of Table2 named as errortable
id,desc,field1,field2,operator
Values of errortable
+----+----------------...
- 121
2
votes
2
answers
1k
views
In what case is a count(x or null) needed in Gaps and Islands?
In this answer, Erwin Brandstetter says:
count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
- 60.4k
3
votes
3
answers
3k
views
Is there a faster way to count JSONB tags?
I'm trying to squeeze some more performance out of this query in Postgres 9.5. I'm running it over 400,000 rows.
In playing around with it, I've noticed that the CASE statements are adding quite a ...
- 541
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
0
votes
1
answer
3k
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 | ...
- 133
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
5
votes
1
answer
428
views
Transformation of row data based on preceding row for special cumulative sum
Following the answer to this question, I have managed to produce the following output to get a running sum of values:
id creation operation value running sum
SyJw-...
- 177
2
votes
3
answers
699
views
calculate and return the ratings using sql
I have a table like this
What I am trying to achieve is weighted rating. For e.g., in this table we have five star as 3, four star as 1, three star as 1, two star as 1 and one star as 0. So the ...
- 167
0
votes
3
answers
245
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
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