Questions tagged [group-by]

GROUP BY is a command in the SQL relational database standard for collapsing a group of rows that share common field value(s) into a single row. Aggregate functions can be performed on other fields in the group, such as SUM() or AVG(), to collate related data into a single value.

2
votes
2answers
19 views

SUM value group by columns but do not 'aggregate' possible?

I want to sum the number of times thresholds were reached according to 2 different values: date and item. Redshift gives me an error asking for more columns in the group by (that I do not want in ...
0
votes
1answer
27 views

SQL - Grouping a timestamp utc column for 30 minute intervals?

Hope your all doing well. I had a query such as below SELECT DISTINCT table_1.id, table_1.city_uuid, cast(table_2.score_rate as decimal(5,3)) as score_rate FROM integrated_delivery....
2
votes
1answer
54 views

How to group by columns?

I am having trouble figuring out how to group and sort rows by column value. My goal is to count the number of UNIQUE 'Package Codes' where column values are orange and blue. There are duplicate '...
0
votes
0answers
28 views

Centos grep -iEo and group & count outputs

I want to group cpanel access log ips for litespeed but U using this code grep -iEo "([0-9]{1,3}[\.]){3}[0-9]{1,3}" /etc/apache2/logs/domlogs/* | sort | uniq -c But I want to see bigger than 10 ...
3
votes
0answers
52 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 ...
0
votes
5answers
28 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 ...
1
vote
2answers
32 views

Python: Group two columns together and find sum of third column

Really new to python and need a bit of help with a question I have to complete. I need to find the average earned per time period (month/year), based on a user input for month (MM) and year (YYYY). ...
0
votes
1answer
19 views

Print name and count of data with most occurrences in python

I need to print the name of a race course with the most occurrences in a column in a csv file. I have tried this: popular_course = (race_data.groupby(race_data.Race_Course.tolist(),as_index=False)....
0
votes
0answers
23 views

How to perform group by on multiple columns in a pandas dataframe and predict future values using fbProphet in python?

My dataframe looks like following. I am trying to aggregate(sum) my amount column based on Date and Group present in pandas dataframe. I was able to successfully aggregate the column. However, I am ...
1
vote
1answer
31 views

How to Group Counted Data

I have 3 categories (Below SLA, Near SLA, Over SLA) that has different conditions, I try to count the data but the result is not summarized by their category This is my query: SELECT B.province ...
0
votes
1answer
33 views

Filter by grouping two columns and sum third column data python

I have an assignment to do for college, where I need to write a python code that lists years, total won, and total lost of bets that took place. My data is in a .csv file with headings ...
0
votes
1answer
29 views

SQL - Finding percentage population between 0 and population average

I am trying to find the percentage of population for each grouped row between 0 and the average of that group. For example, in the below query, say I have a row where num_problems is 100 and average ...
1
vote
1answer
26 views

Sum information with date restriction

I just want to sum by ID and a given value the total depending of a range of dates I tried this: days=(360) import datetime import pandas as pd group = None df=pd.read_excel(r'C:\Users\xxxx\Desktop\...
0
votes
1answer
20 views

How to get cumsum using custom aggregation function in pandas

I have DataFrame like mentioned below df = pd.DataFrame({'year':[2014,2017,2014,2016,2016],'prod':['A','B','C','D','E']}) I can get it using this df.groupby('year').count().cumsum() ## prod ...
0
votes
2answers
37 views

Sum a variable in a grouped dataframe only once for each unique combination of two other variables with dplyr

I have a long table with repeating combinations of area and cluster. counts <- tibble::tribble( ~age, ~area, ~cluster, ~norm.to.area, "gw_25", "cingulate", ...
2
votes
1answer
53 views

PySpark:number group of group type based on observation sequence

I am trying to identify the order of batch types based on their arrival order. I start with this DataFrame +--------+-----+ |sequence|batch| +————————+—————+ | 1| a| | 2| a| | ...
0
votes
3answers
33 views

If two rows have same id but different col2, how can you keep only the ones that have max col3?

I have a table with three columns (id, col2, col3, col4) where col2 is A or B and col3 and col4 are integers. My problem is, there are many columns that have the same id and a different col2 value, ...
-3
votes
1answer
28 views

How to group by values withing different keys?

I have a list and i want to group by within those values. data=[[name:'Test',job:'Dev',exp:2], [name:'Test1',job:'Dev',exp:3]] data.groupBy{ it.job } Result: [Dev:[[name:Test, job:Dev, ...
0
votes
6answers
72 views

SQL GROUP BY with null values

i have these data in an MSSQL 2012r2 table: id data1 amount ------------------ 10 abc 95.00 10 NULL 312.00 20 def 16.00 30 gqi 32.00 Expected query result: id data1 ...
3
votes
1answer
57 views

Spark Dataframes - derive single row containing non-null values per key from multiple such rows

I am a newbie to spark-scala and need some help from the community. This is an app log, each request is scattered in 5 to 6 lines, unique key in all the lines are reqID. Each lines has some columns ...
-3
votes
0answers
26 views

How to find percentile in a group?

I have two columns Category_Id and Qty. I want to find min, 25th percentile, 50th percentile, 75th percentile, max group by Category_Id. Raw Data - 5940084 rows Unique Category Ids - 7084
1
vote
2answers
45 views

Group data by 15 days interval

I need select all users who have placed more than 3 posts in the same category (sql) in the last year with a time interval of less than 15 days. The problem is in the last part "with a time interval ...
1
vote
2answers
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), ...
1
vote
1answer
16 views

Using ROLL UP/CUBE in conjunction with PIVOT [Oracle]

I have a table in Oracle that looks like this: year month customer ------------------------ 2011 Jan Smith 2011 Jan Smith 2012 Feb Howard 2013 Feb Howard ... Now I ...
0
votes
0answers
41 views
+50

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 ...
0
votes
0answers
14 views

Doctrine QueryBuilder result: Group by joined DateTime(Y, m) property in Twig

I'm creating an agenda of 'events' grouped and ordered by 'years' (seasons, to be more precise) and 'months' (September to August). Entities: Event + EventDate (for years and months) Relation: ...
0
votes
0answers
14 views

MySQL- grouping by consecutive entries with same column value

I have a mysql table called employee_jobs that looks something like this: id (uuid) | job_id (uuid) | employee_id (uuid) | date (date) ----------|---------------|--------------------|------------ id-...
0
votes
3answers
44 views

Grouping json content

This is my json: [ { "category" : { "id" : 1, "text" : "cat1" }, "id" : 1, "title" : "book1" },{ "category" : { "id" : 2, "text" : "cat2" }, "...
6
votes
2answers
152 views

Count of values grouped per month, year - Pandas

I am trying to groupby counts of dates per month and year in a specific output. I can do it per day but can't get the same output per month/year. d = ({ 'Date' : ['1/1/18','1/1/18','2/1/18','3/1/...
-1
votes
2answers
39 views

ORA-01722: invalid number in Case statement

Hi I am new to oracle database.In the query below,ZERO_BAL_CODE is varchar2 datatype. I tried to write a case statement, but its throwing an error stating that "ORA-01722: invalid number". Help me to ...
0
votes
1answer
29 views

how to group by parent and apply aggregate function on its childeren in LinQ Lambda

I have a SQL table which holds a parent-child relationship in it own schema via a ParentId column. There are some other tables (Sale, etc.) linked to it via the LocationId column. I am after grouping ...
0
votes
2answers
48 views

R,dplyr: How to replace 0 values based conditional on size of group_by

I am trying to replace the 0 values in a column based conditionally on the size of their group_by with the median value of the group for a large data set. set.seed(10000) Data <- data.frame( ...
1
vote
0answers
34 views

Wrong group query when trying to build dynamically

I am trying to working on a query builder and the example/content is just imaginary. query = from en in Data.InfectionRevision query = from q in query, select: map(q, [:is_complete, :is_active]) ...
-1
votes
0answers
13 views

How to add a column to pandas dataframe with row count per group? Similar to mutate in R

I need to add a column which shows the number of rows per group. In R, this would be easy, using dplyr and mutate. e.g. library(dplyr) mtcars %>% group_by(cyl, gear) %>% summarise(n = n()...
0
votes
2answers
20 views

How to sum a gift amount with out duplicate gifts

I have a table like this-- GiftImpID GiftAmt Category 12345 12.00 Donation 12345 12.00 Donation 56789 10.00 Donation 56789 10.00 Donation 7890 5.00 ...
0
votes
0answers
25 views

Mysql join removing duplicate from the result without using group by

I have 3 table: table "f" (26000 record) +------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------...
-1
votes
1answer
35 views

MySql JOIN performance go down with group by

I have these tables: table "f" (26000 record) +------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------...
1
vote
1answer
34 views

how to get single record per id from one to many related tables in mssql

I have one to many related tables; a product table, and their images per ProductID. Images can exist for a productID single or more or not at all. So I left outer join them if images not exists, ...
0
votes
1answer
22 views

Expand resultset across array values

I'm running some reporting queries and I want to expand the results for each record across a specific set of 4 weeks. This is the current query: select job_id, week, count(*), sum(count(*)) ...
0
votes
1answer
40 views

LEFT JOIN with GROUP BY do not return expected LEFT JOIN result

I'm trying to get all my accumulated sales for each store, even if the value is null (no order for the conditions), but the LEFT JOIN only gives me rows that have a correspondence, which does not suit ...
2
votes
3answers
39 views

Pandas groupby values in a list

I am trying to return a groupby from a pandas df. I want the output values to be summed not merged. But the following merges the appropriate lists. import pandas as pd d = ({ 'Id' : [1,2,2,1], ...
0
votes
2answers
36 views

R: Error in new_quosures(NextMethod()) : could not find function “new_quosures”

Consider a data frame: data = data.frame(a=c(1,1,1,2,2,3), b=c("apples", "oranges", "apples", "apples", "apples", "grapefruit"), c=c(12, 22, 22, 45, 67, 28), ...
0
votes
2answers
31 views

find start end index of bouts of consecutive equal values

given a dataframe df df = pandas.DataFrame(data=[1,0,0,1,1,1,0,1,0,1,1,1],columns = ['A']) df Out[20]: A 0 1 1 0 2 0 3 1 4 1 5 1 6 0 7 1 8 0 9 1 10 1 11 1 I would like to find the ...
3
votes
3answers
35 views

convert grouped data with groupby to dataframes

how can i do to convert my grouped data to data frame. Each group in one dataframe? i did this to group my data I wrote: df = pd.DataFrame({'Animal' : ['Falcon', 'Falcon', ...
2
votes
4answers
42 views

Python: how to groupby a given percentile?

I have a dataframe df df User City Job Age 0 A x Unemployed 33 1 B x Student 18 2 C x Unemployed 27 3 D y ...
0
votes
1answer
25 views

Groupby operations on multiple columns Pyspark

I have applied a groupby and calculating the standard deviation for two features in pyspark dataframe from pyspark.sql import functions as f val1 = [('a',20,100),('a',100,100),('a',50,100),('b',0,...
0
votes
0answers
17 views

Python - Diff of different groups of groupby object

I have this as a csv working in pandas- first ten rows: yearmonth MV Quantiles pead Quantiles ret_f0f1 rf 0 198301 0.2 0.8 -0.071429 0.0069 1 ...
2
votes
0answers
18 views

laravel-query-grouByMonth-groupByYear-sumdata

please help to this some code problem. i want to output the data groupBy month and year and then also the sum of every total in a month this is my code $id = Auth::user()->adoptor_id; $...
-2
votes
1answer
23 views

How to group lists if they have a common sublist?

I have a list that contains many sublists. Each sublist has 30 sub-sublists. How do I merge 2 sublists if they contain one identical sub-sublist? inputdata = [ [[99, 87, 10], [97, 93, 10]], [[97, 93, ...
2
votes
1answer
23 views

MySQL: Data from specific day group by year

I have two columns "date" and "temperature". I want to select average temperature between two specific dates e.g. 1 - 7 April grouped by year. When i use this query select avg(temperature) from table ...