Linked Questions

13
votes
7answers
1k views

Grouping or Window

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp, id_type integer ) ; INSERT INTO tmp ...
9
votes
2answers
29k 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,...
7
votes
1answer
13k views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
3
votes
4answers
2k 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 ...
2
votes
4answers
826 views

How to conditionally append rows to a result set?

I have a complex SELECT query table that identifies relationship between event_id and attribute. Here is a simplification with a VALUES expression: SELECT event_id, attribute FROM ( VALUES (1, '...
4
votes
2answers
1k views

Find changed columns for near-identical rows across tables

I run PostgreSQL 9.3.5 on Windows 7, 64-bit. My data arrives quarterly, in multiple tables (table1, ..., tableN) that are linked, intra-period, by cross-table constraints based on key identifiers. ...
2
votes
2answers
608 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 ...
3
votes
1answer
1k views

Query optimization or missing indexes?

I have this query to aggregate some data from t2 into t1. This is done to optimize the application I am working on so that there are less queries to the database. I chose the below approach to make ...
1
vote
2answers
1k views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) -----------...
3
votes
1answer
960 views

Row number with reset in PostgreSQL

I have this data set in PostgreSQL: User_id | date | is_cleared | ---------------+-------------------------+------------+ C | 2016-08-03 18:28:38.37 | ...
1
vote
1answer
1k 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 "...
1
vote
1answer
622 views

Join and count boolean columns by logical OR

There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday. The desired result is to count the number ...
1
vote
0answers
695 views

Alternative to CASE

What alternatives are there to replace the use of CASE, and will also improve the speed of the query. SELECT created_at, COUNT(CASE WHEN cp > 0 THEN 1 END)::int4 rk, COUNT((CASE WHEN ...
1
vote
4answers
112 views

Query slows with additional FROM item (even with no extra actions)

I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms: SELECT COUNT(body) FROM comment WHERE body LIKE '%[deleted]%'; ...
1
vote
1answer
126 views

About count and or null

I am practising my MySQL in Leetcode and I've encountered this question: The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the ...

15 30 50 per page