Questions tagged [group-by]
7038 questions
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...
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...
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...
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...
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
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 |...
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' :...
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...
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...
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...
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...
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...
-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...
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....
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...