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
0
votes
1answer
36 views

Problems splitting and aggregating data in SQL

Hello I have the following data in csv file: OrderID, OrderType, Amount, Price, Time 1, Buy, 10, 1, 10/01/2018 10:30 AM 2, Buy, 2, 1, 10/01/2018 11:30 AM ...
1
vote
1answer
1k views

PostgreSQL 9.6 performance problem with Sum() aggregate query

I have the following table: id | bigint | not null default nextval('shares_id_seq'::regclass) poolid | text | not null ...
1
vote
3answers
226 views

Beginner SQL: Unexpected results from GROUP BY

I have a sidewalk_deficiency table: create table sidewalk_deficiency ( def_id number(10), insp_id number(10), def_length number(10,2)); insert into sidewalk_deficiency values (10,1,.5); ...
1
vote
1answer
321 views

How to display all the dates in the date-range having group by dates?

Suppose I have a table called “products”: Productive product-name product-quantity created-date 111 ABC 10 1/10/2018 222 XYZ 20 ...
0
votes
1answer
207 views

OLAP window functions in DB2

I am using DB2 z/OS 11.1.5. According to this reference, OLAP functions such as first_value should be supported at least in DB2 9.7. However, when trying to execute the query declare global ...
6
votes
2answers
3k views

WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?

I'm in the process of rewriting queries that no longer pull in all the required data. My question is in regard to a practice I've never seen and haven't found any question on StackExchange that ...
2
votes
1answer
673 views

Aggregate values by interval range given by parameter

I have a table containing an amount column and an index column. I wish to build a single query, which sums up the amount for the given interval ranges for every resulting interval from given start ...
1
vote
2answers
59 views

Dynamic Count of items +/- 15% per row value

I have a table with a column of building sizes named "SQFT". I trying to find the number of buildings that are within 15% +/- of each rows "SQFT" value. I have tried case, count with filter and ...
0
votes
1answer
678 views

Aggregate JSON column

I have a table with a JSON column in Postgres, I am grouping the rows and I am looking for some aggregate function that could "sum up" values from jsons. sum aggregator sums the integers, I am ...
0
votes
1answer
27 views

How do I create a user-defined aggregate function? (Part 2)

I'm refering to this question: How do I create a user-defined aggregate function? My table contains Zero-Values so I have tried the example of ypercubeᵀᴹ but I got the message that (NOT EXISTS (...
0
votes
2answers
260 views

ceil() function behavior in postgresql

While using the ceil() in postgresql, I found some strange behavior of it, Please see SQL : Select *, ceil( (550*0.5)/100 ), ceil( (58*5)/100 ), FROM XYZ If I focus on the output ...
1
vote
2answers
4k views

Get limited data from $unwind mongodb

I have a chats collection which given down below { "_id" : ObjectId("5a44e6545818041cde24aac9"), "messages" : [ { "createdAt" : ISODate("2017-12-23T07:46:29.201Z"), ...
2
votes
1answer
2k views

Find most frequent values for a given column

I have a table that I would like as a leader-board for invitations as described below. I would like to create a query that counts the number of duplicate rows in a given month and order in a ...
0
votes
1answer
118 views

Does Oracle do a native conversion to numbers for varchar2 that are aggregated?

I have a table in an Oracle 11g database that's similar to the following: CREATE TABLE BUDGET_DATA ( BUDGET_MONTH varchar2(30) , BUDGET_CATEGORY varchar2(30) , BUDGET_AMOUNT varchar2(30) ) All ...
1
vote
1answer
2k views

Adding a total sum below a column of prices, and not in its own column

I am running this select query on my database: Select Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate, Sum(bid.bidPrice) over () as TotalBids from Bid inner join Item on bid.ItemId=Item....
0
votes
1answer
56 views

Columnstore Aggregate Pushdown doesn´t work

Recently I started investigating columnstore indexes for some warehouse-behaving part of our system. What I can´t achieve yet is to make aggregate pushdown work. I've checked data types and all ...
0
votes
2answers
1k views

MYSQL - Optimizing MAX(CASE WHEN …)

I have a table similar to the following (Updated: added a type column** Simplified the real query too much I guess) CREATE TABLE versions( type INT NOT NULL, version INT NOT NULL, important ...
0
votes
1answer
83 views

How to aggregate timeseries several months long - when I only want a portion of each month

I have a need to see a trend of data for "month to date" So I want to pull the 1st x number of days of each month for the last 14 months, and the n aggregate this. My data has approximately 10k data ...
0
votes
1answer
736 views

GROUP BY with weighted average

I have a road_insp table: create table road_insp ( insp_id int, road_id int, insp_date date, condition number(10,2), insp_length number(10,2) ); insert into road_insp (insp_id, ...
4
votes
1answer
61 views

Approach to Handling Errors/DataCorrections in Aggregated Data

We're in the process of building a vNext Data/Reporting Platform for capturing Web & Sales Traffic. At a very high level we're capturing Impressions, Clicks & Sales Data which then needs to ...
1
vote
1answer
968 views

Rating count and group rates

I have a rating function, which is working fine but I need to count how many ratings of 5, 4 or 3 there are. SELECT COUNT(DISTINCT rating_rate) AS dis_count, COUNT(rating_post_id) AS total_rates, ...
1
vote
1answer
67 views

Ensuring a GROUP has specific values, no more no less with an aggregate

I have a table (a :: text, b :: uuid). I also have a set (not a list, i.e. order doesn’t matter) of UUIDs, bs = {b1, b2, b3}. How can I get such an a that has exactly those three bs in the table, no ...
4
votes
3answers
2k views

Aggregate function that picks any/first/last/random value

I run a query which looks like below WITH src AS ( ... ) SELECT MAX(field1), array_agg(field2) FROM src I have knowledge that field1 is the same across all rows returned from src so I really don'...
1
vote
1answer
879 views

Most efficient way to aggregate data to new table

Say I have a large table A around 151 million rows: TableA [Id] [bigint] IDENTITY(1,1) NOT NULL, [MetaDataProp1] [int] NULL, [MetaDataProp2] [int] NULL, [MetaDataProp3] [int] NULL, [MetaDataProp4] [...
2
votes
2answers
217 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
271 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 = '...
1
vote
2answers
18k 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
50 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
2k 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
210 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
189 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
416 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
560 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
3k 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
773 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 ...
14
votes
2answers
35k 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
459 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
247 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
145 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
1k 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
607 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
7k 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
103 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 ...