Tag: aggregate-functions

Get count of foreign key from multiple tables

aggregate-functions correlated-subquery left-join postgresql sql

I have 3 tables, with Table B & C referencing Table A via Foreign Key. I want to write a query in PostgreSQL to get all ids from A and also their total occurrences from B & C. Output desired (just the id from A & total count in B & C) : SQL so far SQL Fiddle : The

Aggregate functions across multiple columns in postgres

aggregate-functions postgresql sql

I have a postgresql table with multiple fields containing integers (a1,a2,a3 etc). I want to run aggregate functions(mean, standard deviation etc) across more than one of the columns at once. (Some of them may have a reasonable number of nulls, so I don’t want to just generate column averages and then a…

Why does the following join increase the query time significantly?

aggregate-functions join postgresql postgresql-performance sql

I have a star schema here and I am querying the fact table and would like to join one very small dimension table. I can’t really explain the following: EXPLAIN ANALYZE SELECT COUNT(impression_id), …

Avoid division by zero in PostgreSQL

aggregate-functions divide-by-zero null postgresql sql

I’d like to perform division in a SELECT clause. When I join some tables and use aggregate function I often have either null or zero values as the dividers. As for now I only come up with this method …

Division with Aggregate Functions in SQL Not Behaving as Expected

aggregate-functions crosstab division sql sql-server

I’m trying to do some crosstabs in SQL Server 2008 R2. That part is alright, however, if I try to get percentages for each cell, I run into a problem. Here is a distilled use case: A survey where people give their favorite color and their favorite fruit. I’d like to know how many like a given frui…

Optimal way to concatenate/aggregate strings

aggregate-functions azure azure-sql-database sql sql-server

I’m finding a way to aggregate strings from different rows into a single row. I’m looking to do this in many different places, so having a function to facilitate this would be nice. I’ve tried solutions using COALESCE and FOR XML, but they just don’t cut it for me. String aggregation w…

Problems with Postgresql CASE syntax

aggregate-functions case datetime postgresql sql

Here is my SQL query: When I execute the above query, I get this error: ERROR: CASE types record and integer cannot be matched From the error message I understand that PostgreSQL take the second select, respectively elapsed_time_from_first_login as a row, even if it will always be a single value (because of t…

GROUP BY and COUNT in PostgreSQL

aggregate-functions count distinct postgresql sql

The query: Returns n records in Postgresql: I just want to retrieve the number of records returned: 6. I used a subquery to achieve what I want, but this doesn’t seem optimum: How would I get the number of records in this context right in PostgreSQL? Answer I think you just need COUNT(DISTINCT post_id) …

SQL select elements where sum of field is less than N

aggregate-functions postgresql sql sql-limit sqlite

Given that I’ve got a table with the following, very simple content: I would like to select N messages, which sum of verbosity is lower than Y (for testing purposes let’s say it should be 70, then correct results will be messages with id 1,2,3). It’s really important to me, that solution sho…