Linked Questions

What is the best way to create columns which count the number of occurrences of data in a table? The table needs to be grouped by one column? My database is PostgreSQL. I have seen: SELECT sum(...

I am getting the error subquery must return only one column when I try to run the following query: SELECT mat.mat as mat1, sum(stx.total) as sumtotal1, ( SELECT mat.mat as mat, sum(stx.total) ...

Using the following table: A | B | C | ts --+------+------+------------------ 1 | null | null | 2016-06-15 10:00 4 | null | null | 2016-06-15 11:00 4 | 9 | null | 2016-06-15 12:00 5 | 1 |...

Background I have time series data on a monthly basis and I would like to sum values for each ID, grouped by month and then have the month names as columns rather than as rows. Example +----+-------...

I have a table like this: Name activity time user1 A1 12:00 user1 E3 12:01 user1 A2 12:02 user2 A1 10:05 user2 A2 10:06 user2 A3 10:07 user2 M6 ...

I have a table like this INPUT id author size file_ext -------------------------------- 1 a 13661 python 1 a 13513 cpp 1 a 1211 non-code 2 b ...

I have been asked to solve this question: Say that a big city is defined as a place of type city with a population of at least 100,000. Write an SQL query that returns the scheme (state_name,...

This is a follow-up optimization on Adding a SUM group by into a COUNT DISTINCT query (albeit with some optimizations and joins simplifications). I wonder if it's possible to optimize the following ...

Given a table like: person_id contact_day days_last_contact dash_group 1 2015-02-09 1 1 2015-05-01 81 2 1 2015-05-02 1 2 1 2015-05-03 1 2 1 2015-06-01 29 3 1 2015-08-01 61 4 1 2015-08-04 3 4 1 2015-...

I am creating a report in Postgres 9.3. This is my SQL Fiddle. Basically I have two tables, responses and questions, the structure is: responses ->id ->question_id ->response questions ->...

I've browsed around some other posts and managed to make my queries run a bit faster. However, I've come to a loss as to how to further optimize this query. I'm going to be using it on a website where ...

I've inherited the need to convert a production MySQL DB over to Postgres. This has mostly been handled without issue using simple SQL statements for table/function creation (using Navicat to generate ...

I have this query: Select count(incidentnumber) as average from incidents Where IncidentStationGround <> firstpumparriving_deployedfromstation; I got a result, it's something like 20,000. But ...

I've been stumped trying to optimize this query and was hoping some of you database wizards might have some insight. Here is the setup. Using TimescaleDB as my database, I have a wide table containing ...

The table has an id and step_data columns. id | step_data -------------- a1 | {...} a2 | {...} a3 | {...} Where step_data is nested structure as follows, where the actual keys of the metadata can be ...