26 questions linked to/from Return counts for multiple ranges in a single SELECT statement
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 | ...
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 ...
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 ...
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 ...
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, ...
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 +----+----------------...
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 ...
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 ...
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 ...
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 | ...
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 "...
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-...
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 ...
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 | -...
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 ...