Advertisements select r.id, r.name, sum(p.crop_area) as crop_area_sum, sum(p.total_gross) as total_gross_sum total_gross_sum*10/crop_area_sum as total_normative from placement p right join region r on p.region_id = r.id where r.region_type = 2 and r.parent_id = 255 group by r.id; In the above code, I want to use the variables I got as crop_area and gross_product without summing again to… Read More I need help in performing mathematical operations on the arguments I have selected in SQL

Advertisements I have this object in my products schema: { price: { v1: number, v2: number } } price.v1 is always required and not null, on the other hand price.v2 is not required and can be null. I want to search for the products with price > 20. If the price.v2 is null I need… Read More check the second value if the first is null in Mongodb aggregation

Advertisements Let’s say I have a table as follows: df <- data.frame("interaction" = c("A_B","A_C","D_B","B_A", "C_A"), "value" = c(10,25,6,1,4)) interaction value 1 A_B 10 2 A_C 25 3 D_B 6 4 B_A 1 5 C_A 4 I would like to add values that are referring to the same thing (for instance A_B and B_A are the… Read More Aggregate rows with different order of naming

Advertisements My document: [ { "_id": "5f969419d40c1580f2d4aa36", "users": { "foo@bar.com": "baz", "foo2@bar.com": "baz2" } }, { "_id": "5f9694d4d40c1580f2d4aa38", "users": { "baz@test.com": "foo" } } ] If i use this aggregate, i get two users. Ok. But how can i get only the value of "foo@bar.com"? Test in https://mongoplayground.net/p/3kW2Rw6fSjh db.collection.aggregate([ { "$project": { "users": { "$objectToArray":… Read More Get value of a specific field using aggregation in MongoDB

Advertisements Having a table: id candidateId 1 2 2 4 3 3 4 2 5 5 I’m trying to use the following query: SELECT DISTINCT candidateId, COUNT(candidateId) … which returns only one row: candidateId count(v.candidateId) 2 5 while I want to compute the total count of candidates for each record as follows: candidateId count(v.candidateId) 2… Read More Why does "select id, count(id)" return one row?

Advertisements I have Pandas DataFrame like below: data types: ID – int TIME – int TG – int ID TIME TG 111 20210101 0 111 20210201 0 111 20210301 1 222 20210101 0 222 20210201 1 333 20210201 1 And I need to aggregate above DataFrame so as to know: how many IDs are per… Read More How to aggregate 3 columns in DataFrame to have count and distribution of values in separated columns in Python Pandas?

Advertisements I am trying to perform a sort of aggregation, but with the creation of new columns. Let’s take the example of the dataframe below: df = pd.DataFrame({‘City’:[‘Los Angeles’, ‘Denver’,’Denver’,’Los Angeles’], ‘Car Maker’: [‘Ford’,’Toyota’,’Ford’,’Toyota’], ‘Qty’: [50000,100000,80000,70000]}) That generates this: City Car Maker Qty 0 Los Angeles Ford 50000 1 Denver Toyota 100000 2 Denver Ford… Read More pandas: how to create new columns based on two columns and aggregate the results

Advertisements I have a data like this in MongoDB Collection { date: ‘2022-07-09’, //YYYY-MM-DD amount: 54.77 } { date: ‘2022-07-21’, amount: 50.20 } { date: ‘2022-07-21’, amount: 50.20 } { date: ‘2022-06-26’, amount: 54 } { date: ‘2022-06-06’, amount: 37 } { date: ‘2022-06-06’, amount: 37 } { date: ‘2022-06-09’, amount: 51.77 } and I… Read More Not getting same amount and same date row in group by date mongodb aggregation

Advertisements SELECT STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, SUM(POINTS) * WEIGHT_IN_PERCENTAGE / (COUNT(POINTS) * 100) FROM assignments a JOIN distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY GROUP BY SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME ORDER BY STUDENT_NAME ASC; ERROR at line 1: ORA-00979: not a GROUP BY expression >Solution : You are getting this error for WEIGHT_IN_PERCENTAGE. If WEIGHT_IN_PERCENTAGE is same for… Read More Can you help me to correct this query in sql

Advertisements Theres a several old questions (2015/2016) about doing an upsert with $inc that has a default value. The consensus seemed to be do multiple DB calls. I’m hoping there is a more modern approach to this now. I’m using Mongo 5.0.9. I found this example that states you can now use $cond in update… Read More Mongo 5 findOneAndUpdate aggregation pipeline for default counter value?