Questions tagged [aggregate]

1

votes
3

answer
339

Views

R- reshape2 with aggregation min function

I need to transpose a df in R and the aggregtion function has to be min. Example: library(reshape2) N
GabyLP
1

votes
3

answer
612

Views

How do I make aggregate query return empty set instead of NULL row?

I have a SQL query like this: SELECT t1.name, MAX(t2.value) FROM t2 JOIN t1 ON t1.id = t2.t1_id WHERE t2.t1_id = 1 AND t2.text_id = 16; However, when t2 selection is empty, it returns a row containing NULL values (because of MAX function returning NULL when called on an empty set). I would like it t...
interphx
1

votes
3

answer
138

Views

Spark Scala - Aggregation and Pivoting Based on Time Period

I was trying to implement pivoting similar to sql server in spark As of now, I'm using sqlContext and applying all the transformation within the sql. I would like to know if I can do a direct pull from sql server and implement the pivot funtion using spark. Below is an example of what I'm trying to...
ashok viswanathan
1

votes
3

answer
20

Views

aggregate different rows by different functions in R

I have the following data frame: (dput() for testing bellow) structure(list(V1 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "797 Fleet", class = "factor"), V2 = structure(c(5L, 1L, 4L, 3L, 2L, 5L, 1L, 4L, 3L, 2L, 5L, 1L, 4L, 3L, 2L, 5L), .Label = c("Avai...
user3639100
1

votes
2

answer
121

Views

Postgres GROUP BY returns all rows when primary key is included

I have a simple group by statement where I need to retrieve the primary key id of the row that has the max value of scheduled for the aggregate. select id, max(scheduled) from event group by customer_id, id; This returns all results with the scheduled value as the max(scheduled) for each customer. I...
metame
1

votes
2

answer
140

Views

Pandas: groupby and make a new column applying aggregate to two columns

I'm having a difficulty with applying agg to a groupby pandas dataframe. I have a dataframe df like this: order_id distance_theo bird_distance 10 100 80 10 80 80 10 70 80 11 90 70 11...
Gabriel Macotti
1

votes
1

answer
25

Views

c++11 initializer_list doesn't work for literal constant value of embedded object?

I've got a simple program in c++11: struct A{ int i; struct B{ int i; int j; }; } a = {2, {3, 4}}; g++-7 compiles and gives error: error: too many initializers for 'A' }a={2,{3,4}}; ^ I just wonder how can I declare an object of A using literal constants, how to fix it? Thanks a lot.
Hind Forsum
0

votes
0

answer
5

Views

How to populate and group by subdocuments inside subdocuments in a single aggregation query?

I have these 3 schemas, Payment, Program, and Subscription. const paymentSchema = new Schema({ toUser: {type:Schema.Types.ObjectId, ref: 'User'}, subscription: {type:Schema.Types.ObjectId, ref: 'Subscription', default: null } }); const subscriptionSchema = new Schema({ user: {type:Schema.Types.Objec...
0

votes
1

answer
16

Views

Using an Alias in a SQL Calculation

I have a column called Total_Returned_Value. I want to sum all the individual return values to get the final total return value of all returns. Because of the structure of the table, that column has duplicates so I can't just sum that column. I want to sum that column based on another "id" type colu...
Natan
0

votes
0

answer
12

Views

Troubleshooting Errors with Two SUMs

I have a table, it's going to be used for a supplier scorecard, with eleven different fields that can be assigned a value of 1-5. Null values are allowed. I need to write a query that will calculate the average of the fields that are filled out by each row. In other words, I might be dividing TOTAL...
krebshack
0

votes
0

answer
13

Views

Loop timeseries data

I have data from 2011 to 2016 and each is located in a separate folder as for example in 2011 folder, the 2011 data are located and in 2012, the data for 2012 and the same rule applies to data from 2013 to 2016. I would like to do a for loop. So is there a way that I can do? Thank you for your help...
Sonisa Sharma
1

votes
1

answer
68

Views

“Running Product” aggregate/ windowed function in PostgreSql?

I am trying to normalize End-of-Day stock prices in PostgreSql. Let's say I have a stock table defined as such: create table eod ( date date not null, stock_id int not null, split decimal(16,8) not null, close decimal(12,6) not null, constraint pk_eod primary key (date, stock_id) ); Data in this tab...
Jeremy Holovacs
1

votes
2

answer
53

Views

Create new columns from aggregated categories

I have a dataframe looks like: SK_ID_CURR CREDIT_ACTIVE 0 215354 Closed 1 215354 Active 2 215354 Active 3 215354 Active 4 215354 Active 5 215354 Active 6 215354 Active 7 162297 Closed 8 162297 Closed 9 162297 Active I would like to aggregate the number of active and cl...
hk_03
1

votes
1

answer
115

Views

How to get rid of nested column names in Pandas from group by aggregation?

I have the following code that finds the total and unique sales for each employee using a group by with Employee_id and aggregation with Customer_id. Sales.groupby('Employee_id').agg({ 'Customer_id': [ ('total_sales', 'count'), ('unique_sales', 'nunique') ]}) It is important to know that I will per...
Jane Sully
1

votes
2

answer
45

Views

How to group/sum xts time series by whole seconds

Any package can do the following example? I have big xts dataset in millisecond level. Can I sum the coredata up to second level? The time is index. For example: The ideal result is:
greedIsGood
1

votes
2

answer
41

Views

Aggregating data using pandas python

I have the following data similar to the below: Table 1 Colour Make Red Ford Blue BMW Blue BMW Green Golf Yellow Audi Yellow Audi Yellow Audi Table 2 Colour Make Count Green Ford 5 Blue BMW 1 Green Golf 6 Orange BMW 1 I would like to use pandas to aggregate...
sytup
1

votes
2

answer
62

Views

Using count(), aggregate(), data.table () or dplyr() to summarise the data (mean, standard deviation)

Overview I have a data-set (see below) called "subset_leaf_1" showing how climatic environmental affects the canopy index of a particular oak tree species called "Quercus petraea". I have a column named Urbanisation_index (i.e. data frame below) containing four sub-levels (i.e. 1, 2, 3, and 4). Eac...
Alice Hobbs
1

votes
2

answer
97

Views

Summing cells of some rows and columns

I have a large data frame where some rows have repeated values in some of their columns. I want to keep the repeated values and sum those which are different. Below there is a sample of my data: data
Rafael
0

votes
1

answer
22

Views

How can I print an arraylist that is in one class, by a grouping from another class?

I'm trying to print an arraylist that is in one class, based on one of the parameters from another class. Is this possible? import java.util.ArrayList; public class TVShow { private String title; private String summary; private String releaseDate; private ArrayList episodeList; public TVShow(String...
silverknight52
1

votes
2

answer
100

Views

Looping in select query

I want to do something like this: select id, count(*) as total, FOR temp IN SELECT DISTINCT somerow FROM mytable ORDER BY somerow LOOP sum(case when somerow = temp then 1 else 0 end) temp, END LOOP; from mytable group by id order by id I created working select: select id, count(*) as total, sum(case...
thecoparyew
0

votes
0

answer
22

Views

How to use group by in a case where key values are stored in single cell using commas

I have a case where I have a table event. The event may be of various categories(many) as defined in categories table. The event table contains a column category where the ids of categories that the event belongs to is stored in a single varchar field separated by commas. eg. 2,5,18. In such a case...
Raavan
1

votes
2

answer
898

Views

SQL - Join Aggregated query or Aggregate/Sum after join?

I have a hard time figuring out what is best, or if there is difference at all, however i have not found any material to help my understanding of this, so i will ask this question, if not for me, then for others who might end up in the same situation. Aggregating a sub-query before or after a join,...
Christopher Bonitz
1

votes
1

answer
173

Views

Avoid duplicating calculations when filtering by an aggregate of an aggregate?

I am trying to pull monthly sales of stores which exceeded sales of 10,000 units per month at least 6 months in the past year. My source sales table is daily. Therefore, I am calculating sales for all months for all stores, then figuring out which ones exceeded 10,000 units 6 times, and using that...
ExactaBox
1

votes
2

answer
502

Views

In a matrix, find the mean of column 4 values associated with 20th to 30th percentile values in column 1

Essentially, I want to build a spider plot for sensitivity analysis. I want to split my data into 10 tranches, and find the mean result value (in column 4) for each tranche. The tranches should be selected based on the 10th, 20th, 30th, 40th, etc. percentiles for the data in each of the variable col...
user2024015
1

votes
2

answer
608

Views

MySQL - displaying AVG on more than just one row

If I run a query that includes an Aggregation function (AVG), is there any way I can get that to display on multiple rows? The query I need would be something like: SELECT field1, field2, AVG(field2) FROM tMyTable; The output I need would be something like: field 1 | field 2 | AVG(field2)...
user2000718
1

votes
2

answer
4.5k

Views

find single MAX value per day

I have tired using parts of my previous question which was similar , but I as the table I am querying has multiple rows I can't seem to get one single max value for the day. I will then need to merge this with the previous question, but that's another thing I need to play with... The table is simple...
Eugene Bennett
1

votes
3

answer
3.6k

Views

How to SQL Sum on a field and have a different field in group by section?

I have a table like this: (Please note that Names are not unique and can be repeated, while Personal_ID is unique). ID SourceID Personal_ID Name NumberOfPurchases 1 4 1001 Alex 10 2 2 1002 Sara 5 3 4...
Breeze
1

votes
1

answer
1.1k

Views

Two dimensional aggregate in R? (to create Heatmap)

I have a table with two factor columns that I would like to aggregate into a table that's easy for heatmap mapping. This table has for example has the following format City Date Revenue Costs Manager ____ ____ _______ ______ ___ New York...
Green Demon
1

votes
3

answer
74

Views

Get a column total and a GROUP BY total

In SQL I want to get what % of site hits came from each user. To do this I need to get the sum of the column site hits, but my query uses a GROUP By on another column. How can I get a sum for the entire column, in addition to each user_id in the GROUP BY? Data set: User Page Hits Page ----...
Don P
1

votes
1

answer
1.3k

Views

Pandas: reindex multiindex, broadcast results

I have a multiindex dataframe with sales data for different regions, sizes, and dates. I want to calculate the "worldwide" (over all regions) sum of sales, by size, for each date, then assign that to a column in the original dataframe, with each worldwide value for sales and size broadcast to every...
msteen
1

votes
2

answer
520

Views

How do I define aggregate and aggregate roots and link between aggregates

So I am new to DDD and I am trying to design an application correctly. But I am having a bit of difficulty with identifying aggregate roots. My need is more or less a tree *Customers *Each customer can have 0 or more licenses *Each license can have 0 or more courses *Each course can have 0 or more l...
Rickard Liljeberg
1

votes
2

answer
5.5k

Views

Select columns other than the one specified in GROUP BY clause

Is there a way to select columns other the one specified in the group by clause? Let's say I have the following schema: Student(id, name, age), Course(id, name, credit), Enrollment(student_id, course_id, grade) I want to query for each course the following: course's name, student_count. I came up wi...
0x56794E
1

votes
2

answer
4.8k

Views

Query Performance while using Oracle aggregate function

Below is the query in which I am using an aggregate function. The where clause is simple with an index on corpId and incoming_date . If I simply fetch all rows/count the query takes lesser than a second. However when I use the aggregate function the query takes about 4 minutes. I am using oracle 11i...
user2194253
1

votes
2

answer
1.9k

Views

SQL: not a single group function … not a GROUP BY expression [duplicate]

This question already has an answer here: ORA-00979 not a group by expression 7 answers I have a number of tables detailing a shop's customers and sales, etc. I want to find the minimum sale price; i.e. a single result returned by the SQL expression. In order for the result to make sense I also wan...
user137263
1

votes
1

answer
578

Views

Cut data by date for multiples of break=“min”

I'm using R to aggregate tick data and I have the following function which works well to aggregate the data to the minute but now I want to expound on that and aggregate to 5, 10, 15min. How can I do that? SPY
postelrich
1

votes
2

answer
807

Views

R aggregate over time series, missing dates for some groups

I have a problem when I aggregate data over a date series and a group where some dates are missing in one but not all of the groups. dates
Hugh
1

votes
1

answer
1.2k

Views

Aggregate & count rows that match condition, group by unique values & transform table

There must be a simple and elegant way of doing this in R with data.table package, but I have trouble figuring it out. Vectorized operations are preferable. library(data.table) d1
mel
1

votes
1

answer
66

Views

How to get the STDDEV() of groups of items without the MIN() and MAX() of the group

I have a table like | id | user | bottle | count | | 1 | foo | beer | 2 | | 2 | bar | beer | 5 | | 3 | som1 | beer | 6 | | 4 | som2 | beer | 4 | | 5 | som1 | wine | 1 | etc. How can I get the STDDEV() without the MIN() and MAX() value of count for each grou...
Todor Markov
1

votes
1

answer
579

Views

Exclude some records from aggregate function

Having this piece of SQL code: MIN([Price]) OVER (PARTITION BY [Brand], [Article]) AS MinPrice, Question: how to exclude from MIN() some records, where a.e. [Supplier] != 10?
Arman Hayots
1

votes
1

answer
71

Views

Aggregate functions conflict with some column in my query

I have two tables : users: ___________________________ |user_id | username | |_______________|___________| | 1 | Dolly | | 2 | Didi | |_______________|___________| forum: _____________________________________________________________ |match_static_id| comment...
Basel

View additional questions