Questions tagged [aggregate]

Combining multiple data points into a single data point. This usually is used in reference to SQL and usually to an aggregate function of some sort.

Filter by
Sorted by
Tagged with
2
votes
2answers
249 views

Squashing aggregated rows with PostgreSQL

Given a table my_data like this: id | name | surname | age ----+------+---------+------ 1 | john | smith | NULL 1 | NULL | smith | 32 1 | NULL | NULL | NULL 1 | john | smith | NULL ...
0
votes
2answers
5k views

SQL Server 2008, Sum two columns from different tables

I'm trying to return a table with only 3 columns: CMP, CODE and Totalization. On the first table I need to Sum all the amount data and then group by CODE. Then I query a second table and get the ...
6
votes
1answer
304 views

Columnstore Aggregate Pushdown doesn't work for float/real data types

I am having an issue with Aggregate Pushdown for float/real data types. According to the documentation, Aggregate Pushdown is supported for "Any datatype <= 64 bits" or 8 bytes (): ...
3
votes
2answers
2k views

Accumulate values into an array with Postgres

Currently I have this query: select sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price, sum(case when my_table.property_type_mapped = '...
2
votes
2answers
21k views

Sum of previous n number of columns based on some category

I have following input for which I need to calculate the sum of values for previous x number of weeks for each category. If the x is 3 the output would look like this: Note that the last value is 49 ...
1
vote
1answer
57 views

Tickets created and resolved (or merged) per year

In a third-party application (RT4) running on MySQL 5.5 I have a series of helpdesk tickets represented as records in a table. Each record has a date/time created, a date/time resolved, and a merge ...
2
votes
4answers
3k views

Set non-negative floor for rolling sum, in PostgreSQL

This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data, CREATE TABLE foo AS ...
2
votes
2answers
37 views

Summing “Other” remaining after Top 5

I have the query below which returns the top 5 sum per brokerage SELECT * FROM (SELECT b.brokerage_name AS Brokerage, SUM(c.cost) AS Cost FROM (tblquotesnew q ...
5
votes
4answers
211 views

Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring

I have a road_vertices table: create table road_vertices ( road_id number, vertex_index number, x number, y number ); insert into road_vertices values ('100',1,0,5); insert into road_vertices values ...
0
votes
1answer
202 views

Is there Hypothetical-Set Aggregate Function equivalent to `ntile` in Postgres?

Is there Hypothetical-Set Aggregate Function equivalent to ntile (or some other good solution) in Postgres? I have this query: select frctl ,* from (select * from ...
0
votes
2answers
512 views

Filling Empty Values when Calculating Standard Deviation

I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months. I have a table that contains every date over the past year and ...
0
votes
1answer
579 views

How do I conditionally select elements of an array? [closed]

I have a table (only one row) in my PostgreSQL 9.5 db with two columns i.e., count (bigint) and array (text). count array 6 "112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,...
4
votes
3answers
4k views

Join Multiple Tables for Aggregates

How do I join multiple tables for aggregates with Sum and Count functions? The query I'm trying is as below: Select campaigns.id, campaigns.name, Count(landers.campaign_id) As landers_count, ...
0
votes
2answers
920 views

UNION ALL date on the same row

I am unioning multiple tables together in Postgres, where the timestamp is slightly different, but the same if I round to the hour. Is it possible to make each union show on the same row, if the date ...
19
votes
2answers
48k views

SQL Server returns “Arithmetic overflow error converting expression to data type int.”

When I run this command with SUM() SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total] FROM dbo.t1 AS t WHERE t.Id > 0 AND t.Id < 101; I'm getting, Arithmetic overflow error ...
2
votes
2answers
2k views

Nested aggregate functions over different dimensions

I have a table where I periodically record the throughput of all network interfaces on a set of servers: create table net ( ts timestamptz not null, host text not null, interface text not ...
3
votes
1answer
527 views

Aggregate ranges by merging

There aren't any Aggregate functions for Range types in PostgreSQL. How to aggregate ranges using a merge operation? This solution works but does not look optimal: CREATE TEMPORARY TABLE ...
2
votes
2answers
1k views

How to aggregate transactional records by month year but based on subscription period not calendar months?

I have a table containing transactional events for customers: table: events | id | cust_id | date | amount | |------|----------|----------------------------|--------| | 78 ...
0
votes
1answer
255 views

postgresql: grouping with json columns, eliminating nulls

I have a postgres query that returns a result set simplified as follows, with an id column and two json columns. my_id | col_a | col_b (integer) | (json) | (json) -----------------...
0
votes
1answer
156 views

Get Aggregate Totals

This query was already developed and produces line by line totals, then is copy/pasted into Excel to get Aggregate Totals. I am sure this syntax can be altered to produce Aggregate Totals - I just do ...
4
votes
2answers
2k views

T-SQL to Group time interval change by date range in sql server

The original table has the column Timestamp with Interval Interval: difference in minute between the current and previous Timestamp when sorted by Timestamp itself Timestamp Interval(...
0
votes
3answers
843 views

Percent of total: Is there a more efficient way than using a subselect?

I have an ASSET_SUMMARY_VW view: +-----------------------+---------------+ | ASSET_TYPE | COST | +-----------------------+---------------+ | TRANSPORTATION | 1,000,000,000 |...
4
votes
2answers
8k views

Find Max Value for each month for the last 3 months, properly

I have a query going that gets data for an ID for the last 3 months. I need to tweak it so I get the highest value for each of the three months. I've tried a couple of things with the aggregate ...
2
votes
1answer
55 views

Group by NAME and YEAR, categorize NAME [duplicate]

I have a FINANCE_SOURCE table (I think it might be called a vertical table): +-----------+------------+-------------+--------+------+ | SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR | +--------...
1
vote
1answer
37 views

Calculate not-nulls in group by

I have a TRAILS table: +----+------------+-----------------+ | ID | LENGTH | CONDITION (1-5) | +----+------------+-----------------+ | 1 | 23.1 | null | | 2 | 41.7 | 1 ...
-3
votes
3answers
104 views

can we create a view using SUM or AVG depends on a condition?

I want to get the same result even if I add a column in the GROUP BY section or not for this case : The table contains sale_id,product_id, command quantity and delivered quantity I want to get sum ...
5
votes
2answers
3k views

How to create aggregate function, like sum, with an implicit DISTINCT?

We have an ERP system which allows aggregates to be used (eg SUM(foo)) but not with DISTINCT (eg SUM(DISTINCT foo). Is it possible to create an aggregate function (SUM_DISTINCT), that returns the ...
3
votes
2answers
16k views

Return document that has $max value in specific field using mongodb

I have a json file that has some football results. I need to calculate which team won the most away games, and how many wins there were. My query is this: db.football.aggregate( {"$unwind": "$rounds"...
2
votes
1answer
384 views

MySQL equivalent of a spatial ST_Union aggregate function?

I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the SQL Server function UnionAggregate, or the ...
3
votes
1answer
4k views

Postgres index for aggregate functions

Query: SELECT "places_place"."id" FROM "places_place" LEFT OUTER JOIN "units_unit" ON ("places_place"."id" = "units_unit"."place_id") GROUP BY "places_place"."id" HAVING SUM("units_unit"."...
2
votes
1answer
221 views

How to query chart data in a database agnostic way?

I have several tables with data like this: +---------------------+---------------+ | Creation Timestamp | Numeric Value | +---------------------+---------------+ | 2017-02-01 09:15:00 | 100....
2
votes
1answer
1k views

Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

How do I calculate the percentile of the nth element in a field relative to the ones previous to it in a time series for each subset (partition) of my data? For example, this is exactly what I want ...
2
votes
1answer
419 views

Columns as independent lists of items

So in my database I have items, and those items have categories. I've created a small sample data. I'm creating a report where a user will be able to filter the items by colour, category and some ...
1
vote
3answers
5k views

Pivot time series into date period columns with array aggregate cells

Given a time series of events where each event has a successful or unsuccessful outcome, how do I pivot the ratio of events by entity and time period columns with an aggregate array cell value? I ...
1
vote
3answers
14k views

Influxdb and Grafana combine multiple SELECT

I have some network counters in Influx that I graph with Grafana. I'm trying to combine stats from a couple ethernet interfaces and combine them to show aggregate bandwidth. These two separate ...
1
vote
1answer
62 views

Query to determine percentage composition on a specific grouping of records

I want to analyse the Market capital of the Industry per Counterparty of the following table: -------------------------------------------------- | CPTY | Industry | Value | --------------------...
1
vote
1answer
3k views

Like UNPIVOT in SQL!

How to change the way that cols orders it self? If we have a table like this a b c 1 xx xy 2 xy yx 3 yy xx Since both of b and c are same type I ...
0
votes
2answers
2k views

Query with two LEFT JOIN's is not working as expected

I am using PostgreSQL as my database and I need to write the query that returns: a user_id from 1st table sum of every row's amount from 2nd table (where user_id is the same as in the 1st table) sum ...
3
votes
1answer
19k views

Merge multiple rows into one row with sql

In a source table each row represent hours for one day for a spesific Activity. How can I merge rows of days into rows of periods distinct on Employee, Year, Period and Activity with sql? Select from ...
4
votes
3answers
6k views

GROUP BY gives wrong result with MIN() aggregate function

I have a table that looks like this: +------+--------+----------+------+--------+-------+------+------------+ | id | code | category | mq | weight | weave | show | min(price) | +------+--------+...
13
votes
2answers
6k views

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
5
votes
1answer
2k views

JSON aggregation in a self-join query

I have a database in SQL Server. I need to make a join query on a table itself, the menutable. However, the left table is missing altogether from the result set. I only get the results from the right ...
3
votes
3answers
4k views

Get ID of row in aggregate

I have the following query to get me the values corresponding with the latest date: SELECT MAX(RowAddedDate), X, Y FROM dbo.MyTable GROUP BY X, Y This is fine, but I need to get the ID of each row ...
1
vote
2answers
102 views

Reduce number of rows with same id repeated based on status

I have the following table: -- id, location_id, status, posted_year, posted_quarter CREATE TABLE foo AS SELECT * FROM ( VALUES (1 ,12,'active' ,2014,3), (2 ,12,'inactive',2014,3), (3 ,12,'active' ...
0
votes
1answer
379 views

User Defined Aggregate Functions without DLL [closed]

Is it possible to create a aggregate function without dll? Every aggregate function have to reference a dll? Thank you.
4
votes
3answers
7k views

Median with GROUP BY

Suppose the following table t1: ================= | tag | val | --+ for the sake of simplicity, val is non NULL ================= | a1 | v1 | | a1 | v2 | | a1 | v3 | | ...
0
votes
1answer
193 views

Why can't you do basic operations AFTER an aggregate in a window function? Like (max(x)+5), vs (5+max(x))

The syntax for aggregates and window functions is confusing, this does not work, SELECT (max(x)+5) OVER () FROM generate_series(1,10) AS t(x); While this works SELECT 5+max(x) OVER () FROM ...
0
votes
1answer
119 views

How can I count the students on mongodb

I would like to display the list of students who have passed at least 3 courses at ENSA with more than 60. db.etudiants.insert({ nom: 'emile', prenom: 'josé', cours_reussis: [{ ...
7
votes
2answers
2k views

Grouping on any one of multiple columns in Postgres

Is it possible to create some sort of chain of grouping in Postgres? Let's say I have the following chart: CREATE TABLE foo AS SELECT row_number() OVER () AS id, * FROM ( VALUES ( 'X', 'D', 'G', 'P'...
4
votes
1answer
734 views

Performance considerations for Caching Aggregate counts

We have an InventoryActivity table that holds transactional changes to item quantity: CREATE TABLE dbo.InventoryActivity( InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY, ...