Questions tagged [group-by]

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
1

votes
2

answer
57

Views

Error GROUP BY Dates MysQL

I am trying to group by dates 2 different tables. However, something is going wrong as I am not able to group by dates. I have 2 tables First 'Budget' and Second 'Sales Performance By Region' The Idea is to show the Budget for all the months (12 months) and each month we have data we will see the Sa...
Eduardo Montero
1

votes
2

answer
299

Views

not a Group by expression error in cursor but not in select maybe because of date?

I'm trying to execute the block below in Oracle PL/SQL: declare cursor c1 is select cod_art, to_number(to_char(fecha, 'q')) fech, sum(unidades) suma from tventas group by cod_art, to_number(to_char(fecha, 'q')) order by cod_art; begin for i in c1 loop dbms_output.put_line(i.fech); end loop; e...
Gabriel Serrano
1

votes
2

answer
36

Views

Always get latest record for a user when grouping: SQL

I have a postgres table that looks like this: user_id date val 1 2015-01-01 1 2 2015-01-01 2 1 2015-01-30 7 3 2015-02-01 1 3 2015-02-05 7 3 2015-02-12 3 4 2015-02-10 1 4 2015-02-11 2 I want to be able to group by month...
eljusticiero67
1

votes
2

answer
30

Views

How to return the groups from a double group by that have all categories on them in HiveQL

I have this code in Hiveql and I want to return only the groups that have both Female and Male select first_name, gender, count(*) from attributes group by first_name, gender for example name gender count MICHAEL FEMALE 10000 MALE 11200 and not: name gender count BILLY MALE...
Billy Bonaros
1

votes
1

answer
77

Views

Rails 5.1, group_by, parse date, i18n

I have a very specific problem to get the month name in I18n. I'm grouping some infos per month and year, and it looks like that when I want to translate it, all the month with a specific caracter like é or û aren't working, where the rest is. Let me show you how it looks like : Helper : def user_...
Asso
1

votes
1

answer
197

Views

Naming the group after dplyr's group_by, selecting a single variable

Let's say in my data (e.g. iris), I want to group only one variable-Sepal.Length by Species and add two rows- one at the top (name of the group) 'setosa', followed by observations then after the observations of setosa have ended, a row saying 'END', followed by two blank rows, after which new group...
GWhiz
1

votes
1

answer
19

Views

What is the most optimised way for group by two columns with aggregation and storing the result in a dataframe?

I have a huge DataFrame that includes three columns: kleur, merk, and prijs. I want to compute the average of prijs per kleur for each merk. The data looks as follows: occasions.head(15) kleur merk prijs 0 Zwart Peugeot 1490.0 1 Zwart Alfa Romeo 2450.0 2 Grijs Fiat 3950...
YNr
1

votes
1

answer
54

Views

how to group records in days based on timestamp value from current date in oracle

I Have a table with a column as timestamp with timezone as shown below. desc log_records: id number not null, event_id number not null, event_desc varchar2(2000), log_date timestamp with time zone How do i suppose to group the records like older than 300 days 12 rows older than 250 days 12831 rows...
Anish Gopinath
1

votes
2

answer
48

Views

MySQL Pivot with substitutions

I am trying to take entries from a Forms Plugin and reformat the results into a more usable format for front end viewing. I believe that I can do this with a pivot but I can not figure it out from the examples provided. As a Side note the datatbase includes entries from a number of different forms...
A. Hin
1

votes
1

answer
43

Views

Get 2 rows from a table having same column values in PHP

TABLE id m_id image 1 1 img1.jpg 2 2 img2.jpg 3 1 img11.jpg 4 2 img22.jpg 5 1 img111.jpg ........ etc What i need is - Get only 2 rows from the table group by m_id as below //her...
teashark
1

votes
1

answer
668

Views

Identifying/dropping duplicates in 2 columns based on groupby based on multiple-columns

Looking to first identify then drop duplicates based on identical values across rows in 2 columns. But the caveat is that I to first groupby based on other columns. Please help! Here is a sample data: studentid subj topic lesson testtime responsetime 1 1 math add a tim...
at_ca
1

votes
1

answer
798

Views

Mysql cumulative sum and group by month year

How can I make a cumulative sum of mysql column and group by month year here is my table 'saving' id date value 1 01/01/2018 10 2 01/12/2018 5 3 02/03/2018 6 4 02/23/2018 4 I try this query SET @SumVariable:=0; select CONCAT(LEFT(monthname(date),3),'-', year(date)) as month ,sum(...
user1991131
1

votes
0

answer
119

Views

Backfill data a dataframe with nonuniform timestamp in pandas

Description I have a categorical dataframe which contains nan values at few places. I am trying to perform data cleaning in which I back fill the nan values for 1 day before the label ( error). My dataset look like: dataframe Date Error Type 1 Error Type 2 Error Type 3 2010...
Angel_M
1

votes
0

answer
19

Views

Doctrine 2 how to count the Users that partecipate to an Event? (so a COUNT on GROUP BY)

I have 3 tables in my DB: user (_id, name) event (_id, name, ...) events_partecipants(user_id, event_id) I have two Doctrine entities which maps those tables and their relations and everything works (eg. I'm able to get all the partecipants for a specific event). Now, I want to retrieve the number o...
Noisemaker
1

votes
2

answer
39

Views

Group by giving me a different total count

I am doing an sql query where I retrieve a list over Accounts for a specific year. I thought the below code was fine. SELECT count(DISTINCT ACCOUNT) AS ACCOUNT, CASE when MONTH(Created)=1 then 'January' when MONTH(Created)=2 then 'February' when MONTH(Created)=3 then 'March' when MONTH(Created)=4 t...
MishMish
1

votes
1

answer
34

Views

MySQL: Displaying the last child of the top 5 parent rows (no repeated parents)

i'm trying to get the last 5 messages that have a reply, by user. A user cannot be repeated in the query's result: SELECT message.id_message AS id_message, message.message AS message, user.id_user AS id_user, user.name AS name, user.lastname AS lastname FROM user JOIN message ON message.id_user = us...
Andres SK
1

votes
0

answer
40

Views

Count number of registers in interval & location

Recently I asked how one could count the number of registers by the interval as answered in Count number of registers in interval. The solution works great, but I had to adapt it to also take into account some localization key. I did it through the following code: def time_features(df, time_key, T,...
pceccon
1

votes
1

answer
74

Views

ElasticSearch - Return unique result by field values

I have 3 'places' having each a type and a location: PUT places { 'mappings': { 'test': { 'properties': { 'type': { 'type': 'keyword' }, 'location': { 'type': 'geo_point' } } } } } POST places/test { 'type' : 'A', 'location': { 'lat': 1.378446, 'lon': 103.763427 } } POST places/test { 'type' : 'B',...
woshitom
1

votes
3

answer
84

Views

Group BY Expression column

we're trying to make our table add together all values in column 2 (QtyComp - an expression column of qtyorder * totalqty basically), where they have the same ItemNo (column 1). So, we currently get the below: ItemNo QtyCom 7441 3 7441 1 7441 5 What we want...
1

votes
3

answer
841

Views

MySQL ONLY_FULL_GROUP_BY enabled with CONCAT

I have a table in a database mysql (5.7.21) like this: +----------+--------------+-----------+-----------+ | id_price | id_reference | price_usd | unix_time | +----------+--------------+-----------+-----------+ And I need to extract the average price (price_usd) grouped by week of year, or month (un...
albaserver
1

votes
0

answer
77

Views

Sequelize: group by while including extra attributes on same model

I'm new-ish to sequelize (and dev as a whole), but getting along. I'm trying to make a query in a messaging chat app that can pull all recent conversation threads (1 message from each), done like this in sequelize: const messages = await db.Message.findAll({ group: ['userId'], attributes: ['userId',...
Bluetoothtrooper
1

votes
2

answer
37

Views

Calculating mean of several values based on parameters in other columns [duplicate]

This question already has an answer here: Using dplyr to summarize by multiple groups 2 answers Get the means of sub groups of means in R 4 answers I have a data frame like the following: df
Keshav M
1

votes
1

answer
23

Views

SSRS Tablix Groupings with NULL as Wildcard

My source query produces something like: | timestamp | inUser | outUser | xferUser | taskID | | 2018-03-01 17:00:00.000 | USER.A | NULL | NULL | 1234 | | 2018-03-02 14:00:00.000 | NULL | USER.A | NULL | 2345 | | 2018-03-02 15:00:00.000 | USER.B | NULL | NULL...
Justin
1

votes
1

answer
1.5k

Views

CASE statement with Subquery in SQL Server

Please see the sql query i have. select CASE WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY group BY FACT_ACTIVITY.COL2)>1 THEN (SELECT COUNT(COL3) FROM FACT_ACTIVITY) WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY group BY FACT_ACTIVITY.COL2)=1 THEN (SELECT COUNT(COL4) FROM FACT_ACTTIVITY) END ,EXTRAC...
sparktech
1

votes
2

answer
37

Views

How to get the totale size of all databases

Hi i'm trying to get the totale size of all my databases in GB I started with this command its work well of each one by one but i couldn't get the totale of all databases !! any idea how how to get the totale size of all databases on final. this my command mysql> SELECT table_schema AS 'Database na...
Dr Jay
1

votes
1

answer
66

Views

Match records together in SQL Server by multiple groupings

I have a scenario that I need to 'match' records based on multiple attributes of a person. For instance, if a FirstName and LastName match, or a NickName and LastName match, those two scenarios should be grouped into one larger match. Here's the example data in SQLFiddle: http://www.sqlfiddle.com/...
CodeMonkey
1

votes
2

answer
31

Views

How do I group by where 1 field value differs

I'm new to MySQL, tried looking for the soluion for my problem but didn't quit find it or simply don't understand how to use the given solutions, so I hope you can help me. I have 1 MySQL database table containing the following information. dataset Data Attributename Objectname Location ------...
Mister Tee
1

votes
0

answer
58

Views

Complex Group By Query in Postgresql

I have the below data in a single cursor but I want to write this data into another table with a single row for each member. Query: SELECT member__c, partner_category__c , COALESCE( NULLIF(to_char(activity_transaction_date__c,'Mon'),'') , 'Mon' ) as month ,to_char(activity_transaction_date__c,'YYYY'...
sameer jewalikar

View additional questions