Linked Questions

6 votes
1 answer

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 | ...
Abdul Raheem Ghani's user avatar
38 votes
3 answers

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 ...
孔夫子's user avatar
  • 4,320
10 votes
4 answers

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 ...
Schüler's user avatar
  • 453
2 votes
2 answers

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 AS custom_id, customs.custom_name AS ...
Marco Marcianesi's user avatar
8 votes
1 answer

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, ...
Comic's user avatar
  • 81
2 votes
1 answer

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 +----+----------------...
pakistanimoon's user avatar
2 votes
2 answers

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 ...
Evan Carroll's user avatar
  • 60.4k
3 votes
3 answers

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 ...
ChrisJ's user avatar
  • 541
4 votes
1 answer

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 ...
SzieberthAdam's user avatar
0 votes
1 answer

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 | ...
Sunny's user avatar
  • 133
1 vote
1 answer

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 "...
JAS's user avatar
  • 115
5 votes
1 answer

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-...
Yaneeve's user avatar
  • 177
2 votes
3 answers

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 ...
Lokesh Pandey's user avatar
0 votes
3 answers

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 | -...
MJ.'s user avatar
  • 101
1 vote
1 answer

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 ...
TheRealPapa's user avatar

15 30 50 per page