Questions tagged [group-by]

1

votes
2

answer
57

Views

How to make the mysql query using group by

Below is the table structure I have user_id quote_id bill_type(2=>proforma,1=>invoice) 474 676 2 674 676 2 474 676 1 445 680 2 422 120 2 411 240 2 411 240 2 410 240 2...
Nikhil Gupta
1

votes
1

answer
142

Views

How to make GROUP BY in a cypher query?

I want to translate a SQL query to cypher. Please, is there any solution to make GROUP BY in cypher? SELECT dt.d_year, item.i_brand_id brand_id, item.i_brand brand, Sum(ss_ext_discount_amt) sum_agg FROM date_dim dt, store_sales, item WHERE dt.d_date_sk = store_sales.ss...
Hajer
0

votes
0

answer
11

Views

How to calculate percentage on Outer Apply and Group By?

I have two tables that are being joined with an outer apply. The problem i am having is that i want to calculate percentage based on the values from the left table i.e Table A in the code below. Tried as sub select but doesn't work due to group by Select ISNULL(P.div,'GL')Div, Right(T.Account,9)A...
Khan82
0

votes
1

answer
14

Views

How to compute an index in r?

It is my first time I am asking a question in this great community. I am trying to calculate an index on a data.frame, show them by borough or neighborhood and plot. Which code suited the most? Here is an example of a data set I have. albo, aegy = mosquito species, house = house prospected, The hous...
Armel Tedjou
1

votes
2

answer
27

Views

Parse the dataframe

I have the dataframe something like below in csv format: Country Status People_eligible_Count XYZ True 100000 XYZ False 14000 XYZ Not Ap 360000 I want to turn the above dataframe to below format: Country True False Not Ap XYZ 100000 14000 36000
user2277472
1

votes
3

answer
46

Views

group by year on multiple date columns mysql

I have table as following: hours | ... | task_assigned | task_deadline | task_completion ---------------------------------------------------------------- 123 | ... | 2019-08-01 | - | - 234 | ... | - | 2018-08-01 | 2019-08-01 145 | ... | 2017-08-01 |...
Azima
1

votes
1

answer
40

Views

multi indexing with sort by values - Pandas

I am trying to return the max value based off two Columns in a pandas df. I want to groupby and sort these values so all are displayed from max to min. Here is my attempt: import pandas as pd d = ({ 'Day' : ['Mon','Wed','Sat','Mon','Wed','Sat','Mon','Wed','Sat','Mon','Wed','Sat'], 'Object' :...
jonboy
1

votes
2

answer
36

Views

How can I convert a Group By series to a Dataframe?

I have this DataFrame: import pandas as pd df = pd.DataFrame( { 'Name' : ['Bob', 'Bryan', 'Bob', 'Bryan' , 'Bryan'] , 'Value' : [10,20,15,50,45] } ) Then I got the minimum value per person: df1 = df.groupby(['Name'])['Value'].min() This is quite simple. However, I want to keep working with datafra...
dekio
1

votes
2

answer
25

Views

How to acquire the same result without disabling ONLY_FULL_GROUP_BY

The following query is done after disabling ONLY_FULL_GROUP_BY in MySQL. Now I want the same result without disabling ONLY_FULL_GROUP_BY mode like GROUP BY r.user_id, u.fullname, r.time_taken SELECT u.fullname, ROUND(AVG(r.correct), 2) avg_correct, date_format(r.time_taken,'%d-%m-%Y') time_taken FRO...
w3outlook
1

votes
1

answer
31

Views

Creating a dataframe column resulting from Groupby and transform

I have a dataframe of 4 columns textID, A, B,C I would like create a groupby object and then calculate the 5th percentile on column C and then add this column (calling it 'quantile') back to the original dataframe. I have this following code that works when groupby is on one column df2['quantile']=d...
user11305439
1

votes
1

answer
44

Views

Multilevel group by using lodash

I have below array of objects const array = [ { key: '11111', address: { city: 'NY', country: 'USA' }, className: 'google.com', deviceId: 'aaaaa' }, { key: '11111', address: { city: 'NY', country: 'USA' }, className: 'google.com', deviceId: 'aaaaa' }, { key: '33333', address: { city: 'NY', country...
Profer
1

votes
2

answer
63

Views

SQL group by without aggregation

I'm using PostgreSQL, and I have the following table (simplified for the question): CREATE TABLE SMSNotification ( enduser int, message int, FOREIGN KEY (enduser) REFERENCES EndUser (id), FOREIGN KEY (message) REFERENCES Message (id) ); Now, I'd like to batch send the messages. Hence, I'd like to gr...
Aadit M Shah
-1

votes
4

answer
32

Views

“Pattern Grouping” in SQL

How do you group this data, based on Patterns? Is it possible in SQL? CREATE TABLE ABC ('NAMES' VARCHAR2(50 BYTE)) ` `INSERT INTO ABC (names) VALUES ('CA Apple 3'); INSERT INTO ABC (names) VALUES ('New Apple 4'); INSERT INTO ABC (names) VALUES ('Cra Apple 5'); INSERT INTO ABC (names) VALUES ('UK App...
StackOne
0

votes
1

answer
21

Views

GROUP BY and ORDER

I have a very simple Maria DB for home automation purpose. This is the essential extract of my ER: TelldusActionsPerformed is a log table. It is used for keeping track of very different actions (like a temperature measurements, a door closing, a power switch turned etc) performed at different times....
Hauns TM
1

votes
1

answer
457

Views

SQL Select ID no duplicate rows with same ID

I have a table called MOVIES: id|movie 1 |Batman 1 |Batman 2 |Superman 3 |Spiderman 4 |Ironman 4 |Ironman I want an output like this: 1 |Batman 2 |Superman 3 |Spiderman 4 |Ironman How can i accomplish this ? (This is my current code, which doesnt work) SELECT DISTINCT ID, movie FROM MOVIES GROUP By...
Philipp Weigel
0

votes
0

answer
30

Views

How to convert this sql query into laravel query builder?

I had a table(itemregistrationpangkat) with name(joined from other table itemregistrations), negeri(joined from other table named negeri) and year of start service and end service. I want to get the number of years a person served in a state: ID | Name | negeri | yearstart | yearend...
joun
1

votes
1

answer
31

Views

Join strings in each group and assign back to the original DataFrame

I have dataframe with two columns: user and lang. Each user knows one or more languages: lang user 0 Python Mike 1 Scala Mike 2 R John 3 Julia Michael 4 Java Michael I need to get for each row in user all the languages which he/she knows. I can do that: df.groupby('us...
Oysiyl
1

votes
1

answer
43

Views

Pyspark - GroupBy and Count combined with a WHERE

Say I have a list of magazine subscriptions, like so: subscription_id user_id created_at 12384 1 2018-08-10 83294 1 2018-06-03 98234 1 2018-04-08 24903 2 2018-05-08 32843 2...
DataScienceAmateur
1

votes
2

answer
32

Views

ggplot does not draw line for all of the levels of categories in the data set

I used this code to draw the percent of people in poverty in state of Maine. This state has 17 counties. ggplot only shows 14 of them: ggplot(data=data_ME, aes(x=Year, y=PersonsinPoverty, group=Countyname, shape=Countyname, colour=URBAN)) + scale_shape_manual(values = c(1:17)) + # Set...
Alireza Ebrahimvandi
1

votes
2

answer
31

Views

Grouping by to find average differences for specific indexes in SQL

I have the following table: person_index score year 3 76 2003 3 86 2004 3 86 2005 3 87 2006 4 55 2005 4 91 2006 I want to group by person_index, getting the average score difference betwee...
guyts
1

votes
3

answer
71

Views

GROUP BY statement with a PIVOT command in T-SQL

I have a SQL query that involves a PIVOT command that creates the correct table but now I need to GROUP BY one of the columns. When I try to add the GROUP BY statement, it is returning the following error: 'Column 'PivotTable.1' is invalid in the select list because it is not contained in either an...
C1-14MP
1

votes
1

answer
46

Views

group multiple rows with the same value mysql

I have a certain result-set and I need to group them together on userId. e.g. userId 2019-01-15 2019-01-16 ------------------------------ 132 0 30_140 132 30_140 0 Required output: userId 2019-01-15 2019-01-16 ------------------------------ 132 30_140 30_140 Since...
Azima
1

votes
2

answer
43

Views

How to efficiently perform union of two queries with and without group by

I have a query that performs a union between two select statements one that uses group by and another that doesn't. The problem is I'm selecting the same columns and using the same fucntions in both select statements. It feels Im duplicating the code and I wish to know if there's a better way to wri...
Aravind Balaji
1

votes
5

answer
53

Views

How to find one id with, two same variables and different values

I have to get ID by two values. So let's say when i call: id - 2 i get two values 7 and 6. I want to call 7 and 6 to get value 2 i have table recipeingredient where i should find recipe_id 2 using ingredient_id 6 and 7 example: SELECT * FROM recipeingredient WHERE recipe_id=2 returns: 6 7 but i wan...
DrSkull
1

votes
3

answer
31

Views

Partially convert column in Python dataframe

I have a column in a Python dataframe that looks like the following: Product_ID ABCD 1234 0001234 2345 4567 EFGH It is currently stored as an object datatype. However, I do want the numbers to be consolidated (i.e. 1234 is the same product as 0001234). Since there are also text strings in the column...
Berbatov
1

votes
2

answer
49

Views

SQL Group By on calculated column - invalid identifier

as I am exporting data from DB to a client, I'm facing a SQL issue My working request is : select datmvt as DATE_MVT, (select TRUNC(datmvt,-4) from dual ) as exercice, codpro as CODPRO, SUM(qtemvt)-SUM(C01) as QT from myTable where codpro = 'XXX' and datmvt BETWEEN YYYYMMDD and YYYYMMDD GROUP BY da...
Azrael_404
2

votes
0

answer
15

Views

Optimization of GROUP BY which contains the grouping rules with OR condition

There is some table T1 (in the Oracle database) with some fields A, B, C, D, E, F: Suppose, we need to group our table by the following rule: A & B & (C | D) I think, to solve this task I have to union two grouping queries for groups A, B, C and A, B, D. And then I have to group the result of this g...
John Mullins
0

votes
1

answer
6

Views

Subquery Connecting unique ID to name trouble

I have two tables in question. I am trying to find the names of the pilots who had more than 3 hires assigned in 2018. taxiPilot Table p_id p_lname p_fname p_city 043 Smith John Dayton 044 Doe Jane Cincinnati Assignment Table p_id AT_id HireNo AssignDate City 043 BU...
gracer
1

votes
1

answer
38

Views

sql distinct or group by to get correct order

Okay, so i have a list of posts and some posts are replies to other posts. I'd like to get a list of post parents in reverse order of replies. I've tried group by but it always lists the wrong order and distinct is the only way i've managed to get it to work but obviously then it only lists the post...
Jezzzz
1

votes
1

answer
69

Views

C# GroupBy in query

I have 3 data sets. One for cars (Auto) which is attached to a brand & model (AutoType) and an Owner (Eigenaar) public class Auto : BaseDbEntity { public string Kleur { get; set; } public DateTime DatumGekocht { get; set; } public string Nummerplaat { get; set; } //relations public Eigenaar HuidigeE...
Robbe R
1

votes
1

answer
533

Views

How do I add a title to a grouped pandas dataframe?

I am trying to change the title, the y axis title and the x axis numbers of histograms made from a grouped pandas dataframe. Here is an example of what I am talking about: %matplotlib inline import matplotlib.pyplot as plt import pandas as pd import seaborn as sns from random import randint plt.styl...
laythstag
1

votes
1

answer
289

Views

“DateParseError: Unknown datetime string format, unable to parse: …” using pandas

I have some problems with my code in Python, Here are the test codes: import pandas as pd dict={'Country':['USA','China','Canada'],'Capitol':['Washington DC','Beijing','Ottawa'],'2015-01':[10,20,30],'2015-02':[15,25,35],'2015-03':[20,30,40],'2015-04':[10,20,30],'2015-05':[40,50,60],'2015-06':[20,30,...
Serenity
1

votes
1

answer
30

Views

Determine segregating SQL column in known sets

I have a known target set of records in a large Oracle DB table that I want to pull. Rather than querying them by ID though, I want to find a a column within the table that holds a value(s) that is only assigned to the known set and not assigned to any records that are not in the set, thereby giving...
BradV
1

votes
2

answer
37

Views

Group BY Postgresql

I have two tables: customers(id,first_name,last_name,email) orders (id,order_date,amount,customer_id) customer_id in the orders table is the foreign key for id in the customers table I'm trying to run the following code on PostgreSQL and getting an error: SELECT first_name,last_name,order_date, sum(...
Omer Qureshi
1

votes
1

answer
22

Views

Aggregating Month of Timestamps by User

I have a number of rows in a MySQL table, each with a username (string) and date (datetime) column. I am trying to aggregate the rows by month and year - so my desired output would be like so: Jan-17 Feb-17 ... Dec-18 User1 5 7 2 User2 3 10 6 The way I was pre...
BAK
1

votes
1

answer
223

Views

Why does `summarize` drop a group?

I'm fooling around with babynames pkg. A group_by command works, but after the summarize, one of the groups is dropped from the group list. library(babynames) babynames[1:10000, ] %>% group_by(year, name) %>% head(1) # A tibble: 1 x 5 # Groups: year, name [1] year sex name n prop 1 1...
Jesse Tweedle
1

votes
0

answer
712

Views

EF Core with Postgres - Poor performance compared to same query as raw SQL

I'm trying to diagnose the exact issue with a query that I wrote in C# against a Postgres DB that I've generated a context for in a .NET Core WebAPI project with Scaffold-DbContext. I'm expecting similar speeds for both queries, but when I use the Postgres ODBC driver or PgAdmin to run a query tha...
Justin Capalbo
1

votes
1

answer
43

Views

Grouping Daily data into Weekly data but incorrect grouping when year changes

I have a basic table with daily dates and numeric value for each day. However, after the necessary filters, some dates are missing. I would like this daily data (while missing random days of the week) grouped weekly where beginning of the week would be sunday and ending of the week would be saturday...
Nick Momin
1

votes
0

answer
24

Views

Search by date even if date is not in table

I make a select in MySQL and search between 2 dates and make a count of elements by date and it works fine but, if a date has no records it does not appear, what I'm looking for is something like this: Column date / Count 2017-01-01 / 1 2017-01-02 / 2 2107-01-03 / 0 *Date without r...
1

votes
2

answer
233

Views

setting with copy warning in python method chaining with df.groupby.transform() [duplicate]

This question already has an answer here: How to deal with SettingWithCopyWarning in Pandas? 11 answers My need is to get a new column created in pandas dataframe which is count and sum based on grouping. I am using method chaining like below. df['total_sum']=df.groupby('column1')['column 2'].trans...
Atharva Pargaonkar

View additional questions