Questions tagged [aggregate]

1

votes
0

answer
34

Views

How to search multiple collections in mongoDB [duplicate]

This question already has an answer here: How to Match on Joined Collections Using Laravel and MongoDB? 1 answer How to join to two additional collections with conditions 1 answer Aggregate $lookup Total size of documents in matching pipeline exceeds maximum document size 1 answer I have 2 collec...
Harjeev Singh
1

votes
1

answer
58

Views

How to count length of NA values by group/factor in R?

I am tasked with manipulating data obtained from 1258 unique surveys. In terms of dimensions. 28 million individual observations (including NA) -8 columns (variables). object name : dat The column/variable I am particularly interested in is education (edu). I want to get the length of NA and Non-N...
Shivy b
1

votes
0

answer
108

Views

How to do Shard Aggregation on DynamoDB

Referring to https://www.youtube.com/watch?v=xV-As-sYKyg&index=23&t=2758s&list=PLq2BHGbGb1H9wf6XktfGJ8b3vFt-c4H7W I am sure many of you have seen this image. A technique to gain better write throughput by adding random number to the hash-key On the read side, we need to sum up the individual query r...
Bluetoba
1

votes
1

answer
34

Views

Using Both a Column and an Aggregate Result Set to Join Two Queries

Oracle Beginner here trying to return the highest-paid employee from each store. Is it possible to return this result by executing an INNER JOIN ON MAX/SAL+COMM expressions and a second column (store_id), similar in format to the first query below (two-column join)? The Alternative Solution returns...
Murf
1

votes
1

answer
242

Views

Tableau Weighted Average Per Capita Calc not aggregating right

I am trying to create a simple revenue per person calc that works with different filters within the data. I have it working for a single record, however, it breaks and aggregates incorrectly with multiple records. The formula I have now is simply Sum([Revenue]) / Sum([Attendance]). This works when I...
J Grun
1

votes
1

answer
37

Views

MySQL Find minimum price row for multiple same product

I have product table with many same product but different condition and different supplier/vendor and i want to get a product with cheapest price and it's other data. Here some Entry +----------+--------------------+-----------+------------+------------+--------------+-------------+ | ID |...
Humaira Naz
1

votes
2

answer
55

Views

Max position of aggregated dataframe

I want to get the maximum positions in a split by factor data frame, so that I can take the values of these positions in an additional dataframe. As example. Let's say I have this DF: df1=data.frame(groups=c('a','a','b','b','b','c'), c1=c(1:6), c2=c(2:7), c3=c(4:9)) print(df1) row. groups c1 c2...
keremw
1

votes
0

answer
27

Views

Is there a way to merge identical excel tabs into one dataframe using python?

I'm extremely new to python, and am having some issues attempting to merge identical excel tabs (same column labels and data) into a aggregated tab. I've been looking through various resources, and this link appears to be the best resource, but it doesn't quite mesh with the way I've been shown. I...
Getoffmylawn
1

votes
0

answer
56

Views

Building Monthly aggregate from Daily table

I have written the code below to check for unique customers for the last 30 days. How would I repurpose this code to check for unique customers on a month start date. I am trying to build a monthly aggregate using table_billing that has a daily grain. Can you please guide me. select 'context.proces...
sqlstacking
1

votes
1

answer
28

Views

Access 2013 SQL, three tables, two using sum wrong results

Can someone please help me with this issue? I've scoured the Internet looking at dozens of examples, but i just can't find a solution that works. I am using Access 2013. The problem is that I am trying to make a query that will highlight all part numbers from a supplier that either has customer back...
Alan Hunter
1

votes
1

answer
57

Views

Table aggregation using linq (calculate Average value)

How to Aggregate below table using linq query Date tagname value 06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(4) 54.73497 06-06-2018 14:15:00 Poll.Registers Block 0.310-PT-304_(5) 3.417564 06-06-2018 14:15:00 Poll.Registers Block 0.310-PT...
user7417866
1

votes
0

answer
60

Views

Creating max and mean row in a matrix table php

I need help in creating a row for max and mean per column(month), I have tried creating a row for max but it is retrieving the max value for the whole year per column. I also like to know if the approach would be the same if the aggregate functions are to be added as columns, let's say my new matrix...
JL Tan
1

votes
1

answer
211

Views

Django query for total balance from different currency wallets

What is the best way to get total balance of the user wallets with different currencies? myapp/models.py from django.db import models from django.contrib.auth.models import User USD = 'USD' EUR = 'EUR' GBP = 'GBP' CURRENCY_CHOICES = ( (USD, 'US Dollars'), (EUR, 'Euro'), (GBP, 'UK Pounds'), ) class W...
Egor Bolotevich
1

votes
0

answer
2.6k

Views

Aggregate method in MongoDB Compass?

as stated in the title i'm having some problems querying from MongoDB Compass using the aggregate methhod. I have a collection of documents in this form: {'Array':[{'field':'val','field2':'val2'},{'field':'val','field2':'val2'},{'field':'val','field2':'val2'},{'field':'val','field2':'val2'},{'field'...
1

votes
1

answer
68

Views

Mongoose not returning data for aggregate function

I have route that uses the following function to sum up the total revenue from registrations. It works just fine in the shell and via Studio3T but mongoose API doesn't return anything. I am running mongoose 5.0.17 and using Azure Cosmos DB. exports.getDhashboard = function (req, res) { Registration....
Kameron
1

votes
0

answer
67

Views

HBase SingleColumnValueFilter doesn't work in AggregationClient's avg method

I'm trying to get an average value of some qualifier which is written as BigDecimal type in HBase for key rows with specific prefix. @Test(timeout = 300000) public void testAvgWithFilter() throws Throwable { AggregationClient aClient = new AggregationClient(conf); Scan scan = new Scan(); scan.addC...
Michael
1

votes
1

answer
45

Views

Aggregate function causing errors in SQL

Trying to establish a production percentage based on a couple of values from the same table with the following statement: SELECT avg(sum([Actual]/ (sum(NULLIF([Team Members],0)/(sum(NULLIF([Actual Min],0))))))) FROM [Shift Production Log] WHERE [Date] >= DATEADD(dw, -2, :dvalue) AND [Line] = :Lpram...
Dave Holt
1

votes
0

answer
54

Views

How to get sum of nested array in mongodb aggregate?

My Array is like this, [data] => Array ( [0] => Array ( [test] => 164.765 ) [1] => Array ( [test] => 26.1 ) [2] => Array ( [test] => 194.564 ) [3] => Array ( [test] => 54 ) [4] => Array ( [test] => 99.753 ) [5] => Array ( [test] => 32.735 ) [6] => Array ( [test] => 2715.865 ) [7] => Array ( [test] =...
Vatsal Patel
1

votes
2

answer
41

Views

SQL Server CREATE AGGREGATE logic error

I've created a CLR funtion for SQL Server 2014 that should calculate subtraction between the first and the last value in column [Value]. Here is the table: Date_Time Value ------------------------------------- 2018-03-29 09:30:02.533 6771 2018-03-29 10:26:23.557 6779 2...
Ken Crow
1

votes
1

answer
33

Views

How to distinct (count) value when value is nested in array?

I have a data sample something like this: 'diagnostics' : { '_ID' : '554bbf7b761e06f02fef3561', 'tests' : [ { '_id' : '59d678064e4645ec562a37e2', 'name' : 'RBC', }, { '_id' : '59d678064e4645ec562a37e1', 'name' : 'Calcium', } ] } I want to get all distinct _ID and count of all test groups in with...
Siddhant Vats
1

votes
2

answer
553

Views

mysql pivot table with string values

I have a table with just two columns like this mysql> select * from stuff_table; +------+-------+ | name | stuff | +------+-------+ | John | shoes | | Jim | bag | | Ely | book | | Tom | bag | | Will | shoes | | Pitt | book | | Dean | bag | | Luke | bag | +------+-------+ and I tried wit...
Yarp
1

votes
4

answer
65

Views

SQL subquery to fetch rows by column values

I have a PostgreSQL table like this: CREATE TABLE foo(man_id, subgroup, power, grp) AS VALUES (1, 'Sub_A', 4, 'Group_A'), (2, 'Sub_B', -1, 'Group_A'), (3, 'Sub_A', -1, 'Group_B'), (4, 'Sub_B', 6, 'Group_B'), (5, 'Sub_A', 5, 'Group_A'), (6, 'Sub_B', 1, 'Group_A'), (7, 'Sub_A', -1, 'Group_B'), (8,...
Dinesh Ahuja
1

votes
1

answer
48

Views
1

votes
0

answer
31

Views

MongoDB - Find accounts that match one field but do not match a second field

Okay, so I'm trying to identify a list of accounts where they have matching SSN, but DOB is different. Fields in question are: account_num, ssn, dob. In the end, I need the account_num for those accounts which has more than 1 accounts that match the SSN, but has a different DOB. I hope that makes s...
user179421
1

votes
1

answer
17

Views

Grouping results from a group in mongodb [duplicate]

This question already has an answer here: Count occurrences in nested mongodb document and keeping group 1 answer I have this aggregate function: [{ $group: { _id: { rl: '$rl', status: '$screener_status' }, total: { $sum: 1 } } }, { $project: { _id: 0, rl: '$_id.rl', status: '$_id.status', total: '...
amedeiros
1

votes
1

answer
151

Views

$lookup with case sensitive fields

I know how to do the case insensitive search. example : members.email: [email protected] BUT : users.email: [email protected] db.getCollection('members').aggregate([ {$lookup: {'from': 'users', 'localField': 'members.email', 'foreignField': 'email', 'as': 'users'}}, {$unwind: {path: '$users', preserveNullAndEmp...
Kaman Wu
1

votes
2

answer
215

Views

MongoDB - $lookup and $aggregate in 2 collections

I have two collections like this : 1st collection name is Promotions: { '_id': 'A019283847466', 'code': 'AAA', 'aliases' : [ 'AAA1','AAA2','AAA3'] } 2nd Collection name is PromotionUsages: { 'customerId': '_1234567890' 'code': 'AAA1' } { 'customerId': '_0987654321' 'code': 'AAA1' } Expected output i...
1

votes
2

answer
34

Views

Getting data from two different tables in mysql

I have two tables, the first table is 'registration' table, the second table is 'messages' table, the registration table has a column for 'reg_id(primary key)', and 'membership(either 'participant' or 'graphics)'' while the messages table has a column for 'message_id(primary key)', 'reg_id(foreign k...
atomty
1

votes
0

answer
90

Views

How to use filter to replace elemMatch in aggregation

I have a find query in mongoose, but for need i want now to use aggregation, after search i find that elemMatch didn't work with aggregation and i must use filter, is there a solution to replace in my query with filter I use regex on my query : my query mymodel.find({u_info: {$elemMatch: {ai_id: 12...
user2285831
1

votes
2

answer
206

Views

Async/await functionality for db.eval aggregate

I am trying to execute a db.collection.aggregate() query within a call to db.eval(). I am using eval() because I am making a dynamic number of lookups, so I generate the query by concatenating relevant strings. The query works perfectly when I manually remove the quotes from the string: await db.col...
Janet Lee
1

votes
1

answer
224

Views

Aggregate character variables

I have the following structure for my database: ZIP Category 1 Rouge 1 Vert 1 Rouge 2 Violet 2 Rouge 2 Noir 3 Vert 3 Violet 4 Rose From it, I want to create the following database ZIP Category Times 1 Rouge,vert,rouge 3 2 Violet,rouge,noir 3 3 Ver...
kat
1

votes
2

answer
74

Views

BASIC SQL - Struggling with Group By Statements and Summing [duplicate]

This question already has an answer here: SQL Server - Column “invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause” 3 answers I am writing the following SQL script, and am encountering the error listed directly below said script: SELECT...
James
1

votes
0

answer
232

Views

Pandas pivot_table drops values for aggregate functions that return all None, even if dropna argument is False

The dropna=False argument of pandas pivot_table is not doing what I expect it to do. I'm starting with the following DataFrame: import pandas as pd # pd.__version__ = '0.23.3' df = pd.DataFrame({'fruit':['apple','orange','peach','apple','peach'], 'size': [1,1.5,1,2,1], 'taste': [7,8,6,6,10]}) ...an...
tomp
1

votes
1

answer
97

Views

Using the “aggregate” function for drawing line plots

I am trying to draw a line plot with error bars for two groups of data (Treatment vs. Control). There are totally 20 periods, 10 Trial Periods (TP) and 10 formal Periods (P) and I want to show how the group means change over time. For simplicity, the following dataframe includes 3 Trial Periods (T...
Yvvone Ding
1

votes
1

answer
33

Views

Mysql: create procedure with various selects through distinct value cycle

I've got a table like that TIME |STRCOUNTRY |STROPERATOR | NINCOMINGCALLS 300718|RUSSIA |MTS | 2 300718|RUSSIA |Megafon | 3 300718|UK |Vodafone | 1 300718|UK |UKTele | 3 And it the end I want to show: calls for operator, sum for all operators by country...
1

votes
2

answer
177

Views

R Aggregate multiple rows

My question seems to be a very common question, but the solutions I found on internet don't work... I would like to aggregate rows in a data frame in R. Here is the structure of my data frame (df), it is a table of citations : Autors Lannoy_2016 Ramadier_2014 Lord_2009 Ortar_2008 Burgess E...
Joël
1

votes
0

answer
30

Views

Get value by key in Array Object to Array in mongodb & mongoose by query

I have a question about mongodb. I have a collection with documents: { '_id' : ObjectId('5b5445b44ae5aa0c0c625378'), 'skills' : [ { 'skillId' : 11, 'level' : 10, 'available' : 1, '_id' : ObjectId('5b5445b44ae5aa0c0c62537c') }, { 'skillId' : 12, 'level' : 10, 'available' : 1, '_id' : ObjectId('5b54...
1

votes
1

answer
61

Views

Microsoft Access - how change the label of the aggregate function “Totals” in its row

I've been trying to do some exercises from a book teaching on how to use MS Access (in my case 2016 version is utilized). In one of the sections, an example is given on how to perform a quick calculation using the aggregate function by clicking the 'Totals' buttons from the Record group in the HOME...
G.M.
1

votes
2

answer
124

Views

Total percentage of a series of positive and negative percentages

Postgres 9.6.6, latest Ubuntu LTS. I have a column with a daily grow (+-) percentages, like: Trader_Id Date 8_AM 8_PM Growth% 1 1/1 290 248 -14,48 1 2/1 225 880 291,11 1 3/1 732 512 -30,05 1 4/1 621 602 -3,06 1...
user3262353
1

votes
0

answer
32

Views

How to Combine Aggregate + Lookup and Search in Laravel

I Have the following Aggregate + Lookup Query Aggregate + lookup Query $collection = Dapil_Kotakab::raw(function ($collection) use ($page, $perPage) { return $collection->aggregate([ [ '$lookup' => [ 'as' => 'KecDetails', 'from' => 'src_kecamatan', 'foreignField' => 'id', 'localField' => 'idKecamata...
Nurjaman

View additional questions