Questions tagged [group-by]
7010 questions
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...
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...
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...
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,...
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...
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(...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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_...
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...
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...
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...
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...
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...
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...
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(...
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...
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...
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...
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...
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,...
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',...
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...
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',...
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
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...
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...
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...
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/...
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
------...
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'...