Questions tagged [group-by]

0

votes
0

answer
29

Views

C# Linq equivalent for SQL script not responding

I have the following SQL script which works fine and pretty fast: select top 30 CONVERT(date, p.DateCreated) as Fecha, (select count(*) from People d where d.recipientid = p.recipientid and d.SubscriptionType = 0 and CONVERT(date, p.DateCreated) = CONVERT(date, d.DateCreated)) as Subscribed from Peo...
parismiguel
1

votes
2

answer
21

Views

Show top N scores in MySQL 8 without duplicates by category

I have the following table in MySQL 8.0.15: CREATE TABLE golf_scores (person TEXT, score INT); INSERT INTO golf_scores VALUES ('Angela', 40),('Angela', 45),('Angela', 55),('Peter',45),('Peter',55),('Rachel', 65),('Rachel',75),('Jeff',75); SELECT * FROM golf_scores; +--------+-------+ | person | scor...
user1717828
1

votes
2

answer
22

Views

Printing a groupby object's content

I have a table like fruit day --------------------- apple Mon apple Mon apple Tue banana Tue I want to group by on fruit and have the unique days in a list and another column for the len of the list. I'm familiar with how to do groupby but not sure how to get those uniqu...
John Constantine
0

votes
1

answer
21

Views

Trying to fill NaNs with fillna() and groupby()

So I basically have an Airbnb data set with a few columns. Several of them correspond to ratings of different parameters (cleanliness, location,etc). For those columns I have a bunch of NaNs that I want to fill. As some of those NaNs correspond to listings from the same owner, I wanted to fill some...
J.Doe
0

votes
0

answer
7

Views

How to filter minimum distinct values in a group by?

Lets say I have a table with the following columns (A,B,C) How would I write a pig statement to create a group by on a column( A). And then filter where count(column B > 100) and count of (distinct(column C) > 3) ? From what I have: I first removed count where B is less than 100 filter_column = FIL...
J Doe
-1

votes
1

answer
13

Views

MySQL Group-By query

I have the following working MySQL script: SELECT CAT.LCDescript as Category, Labor.CAT, Labor.LabDescript As Task, ProjLab.PLqty As Qty, ProjLab.Plucost As UnitCost, ProjLab.PLqty*ProjLab.Plucost as TotalCost FROM `ProjLab` INNER JOIN Labor ON ProjLab.Lid = Labor.Lid INNER JOIN CAT ON Labor.CA...
Pick Avana
1

votes
2

answer
18

Views

Show top N rows by category in MySQL 8 without duplicates in another category

Similar to this question, I have the following table in MySQL 8.0.15: CREATE TABLE golf_scores (id INT PRIMARY KEY AUTO_INCREMENT, person TEXT, score INT, age INT); INSERT INTO golf_scores (person, score, age) VALUES ('Angela', 40, 25),('Angela', 45, 25),('Angela', 55, 25),('Peter',45, 32),('Peter',...
user1717828
1

votes
2

answer
140

Views

Pandas: groupby and make a new column applying aggregate to two columns

I'm having a difficulty with applying agg to a groupby pandas dataframe. I have a dataframe df like this: order_id distance_theo bird_distance 10 100 80 10 80 80 10 70 80 11 90 70 11...
Gabriel Macotti
1

votes
2

answer
46

Views

SQL: Do columns in the Group By matter once a Unique Grouping is reached?

Because of the error, "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I'm wondering if, after so many groupings, the columns listed in Group By no longer affect the query. I have placed a simply query below for example. Sele...
user3841709
1

votes
1

answer
34

Views

pandas hwo to groupby create other columns by counting values of existing columns

I got to know how to do this in R( How to make new columns by counting up an existing column), but I'd like also to know how it works in python as well. When the original table is like below userID cat1 cat2 a f 3 a f 3 a u 1 a m 1 b u...
user9191983
1

votes
1

answer
51

Views

LINQ Group by different output

I am having a hard time grouping a dbset (EntityFramework) by two fields and sending that output to a strongly typed view. When I use an anonymous type for the composite key I get the right output. A list containing one item and that item in turn has two or more grouping items. Now if I use a class...
Edward Sanxs
2

votes
3

answer
18

Views

Last occurrence of a Groupby object under certain conditions

Let's say I have a DataFrame that looks like this: Categories Values 0 Category 0 1 1 Category 0 0 2 Category 0 -1 3 Category 0 0 4 Category 1 1 5 Category 1 0 6 Category 1 -1 7 Category 1 0 8 Category 2 1 9 Category 2 0...
mathguy
1

votes
3

answer
16

Views

Finding the row corresponding to the Nth value in each group by in SQL

I have a table A as follows: name course grade Bob C1 12 Bob C2 13 Bob C3 23 Bob C4 17 James C2 15 James C6 27 Nick C5 18 Nick C1 16 Nick C3 22 Nick C2 32 Nick C7 19 I want a query that can return the student na...
James Robisnon
1

votes
2

answer
605

Views

c# Linq - Select Top 2 from Each Group

I need to get the Top 2 Products for each ProductTypeName : var productDetails = (from p in products join po in productOrganisations on p.Id equals po.ProductId where po.OrganisationId == id where p.ProductTypeId == (typeId > 0 ? typeId : p.ProductTypeId) //filter by type if specified where p.IsLiv...
fourbeatcoder
1

votes
1

answer
914

Views

Linq To Entities Group By with multiple tables in key

With a table structure along the lines of: TableB TableC I need to run a query in Linq to EF that does this: SELECT SUM(TableC.SomeColumn) FROM TableA a INNER JOIN TableB b ON a.Id = b.fkAId INNER JOIN TableC c ON a.Id = c.fkAId WHERE GROUP BY c.Col2, c.Col3, b.Col5 I have this so far, but I can't...
Stephen York
1

votes
1

answer
51

Views

Assign Column Value by fetching Max Value of Pandas Group Count

I have the following Dataframe. Year Sector Number Veh Types Month Count 2015 AA 173 F Apr 277 2015 AA 173 F Aug 277 2015 AA 173 F Dec 277 2015 AA 173 F Feb 277 2015...
ceeka9388
1

votes
2

answer
284

Views

Find minimum daily value using pandas GroupBy or pivot_table

I have a Dataframe obtained from a csv file (after some filtering) that looks like this: df3.head(n = 10) DateTime Det_ID Speed 16956 2014-01-01 07:00:00 1201085 65.0 16962 2014-01-01 07:00:00 1201110 69.5 19377 2014-01-01 08:00:00 1201085 65.0 19383 2014-01-01 08:00:00 1201110 6...
VivianT
1

votes
1

answer
40

Views

Pandas compute datetime diff, but for each user

Dataset is related to time user spent on viewing items: user_id item_id view_started 121 160 2015-10-20 17:02:02 231 160 2015-10-18 11:02:29 231 161 2015-10-18 11:05:23 121 166 2015-10-18 11:04:34 231 180 2015-10-18 11:06:...
Null-Hypothesis
1

votes
2

answer
142

Views

Postgres GROUP BY Array Column

I use postgres & have a table like this : id | arr ------------------- 1 | [A,B,C] 2 | [C,B,A] 3 | [A,A,B] 4 | [B,A,B] I created a GROUP BY 'arr' query. SELECT COUNT(*) AS total, "arr" FROM "table" GROUP BY "arr" ... and the result : total | arr ------------------- 1 | [A,B,C] 1 | [C...
Raditya Arya
1

votes
3

answer
44

Views

SQL Group by 2 column

I want to display once in multiple rows with the 2 GROUP_BY My table is like this +----+----------+-------+ | ID | BRAND | TYPE | +----+----------+-------+ | 1 | A | Clothes | | 2 | A | Hat | | 3 | A | Hat | | 4 | A | Hat | | 5 | B | Jeans...
Beadyyy
1

votes
3

answer
56

Views

Oracle SQL How to Count Column Value Occurences and Group BY during joins

I'm working on another SQL query, trying to group a collection of records while doing a count and joining tables. See below for goal, current query, and attached scripts for building and populating tables. Show all customers who have checked more books than DVDs. Display customer name, total book ch...
Ryan Cohan
1

votes
3

answer
43

Views

I want to find the last transaction for each account prior to a certain date

I have a table which is defined (on Azure SQL Server) as follows: CREATE TABLE dbo.[transaction] ( id INT IDENTITY(1,1) NOT NULL, [date] DATETIME NULL, amount FLOAT NULL, balance FLOAT NULL, account_id INT NULL, CONSTRAINT PK__transact__32F PRIMARY KEY (id) ) I want to find the last balance for eac...
Dirk R
1

votes
1

answer
27

Views

Normalize data by first value in the group

I have a DataFrame of 6 million rows of intraday data that looks like such: closingDate Time Last 1997-09-09 11:30:00-04:00 1997-09-09 11:30:00 100 1997-09-09 11:31:00-04:00 1997-09-09 11:31:00 105 I want to normalize my Last column in a vectorized manner by dividing every row by...
Évariste Galois
1

votes
1

answer
44

Views

Python itertools.groupby() using tuples with multiple keys

I'm trying to read through a tuple and sort it. I want to group by the first word in the lists, and keep the lists with the smallest third word. Then I want it to return the entire list for those that were kept. I found a very useful example here, except I'm looking to do this with lists with three...
Kelsey
1

votes
4

answer
53

Views

group by only those values that are LIKE '[list of 3 values]'

I have pandas dataframe. I want to select values from col1, that should have all 3 values in col2. col2_values_should start_with = [P1.adv, P2.cmp, P3.part ] Also, since you can see the later part of values in col2, is dummy. so I need to use similar option as LIKE operator of SQL in order to chec...
frozen shine
1

votes
3

answer
48

Views

how to output result of group by of two columns with one column values as row and another as columns?

I have table like this id col1 col2 1 A 1 2 B 0 3 A 1 4 C 1 5 B 0 6 A 0 7 C 1 8 C 1 9 B 1 10 B 0 I need a query something like this Values 1 0 A 2 1 B 1 3 C 3 0 In the above result the heade...
1

votes
1

answer
36

Views

Grouping on identical column names in pandas

time A1 A1 A2 A2 A2 A3 A3 2017-01 a1 a2 b1 b2 c ..... 2017-02 a3 a4 b3 b4 c 2017-03 a5 a6 b5 b6 c .... There is a dataframe as shown above. How to get mean value of the columns which have the same name( as shown below)?...
xianyuyu
1

votes
3

answer
15.9k

Views

inner join + count + group by

I'm have trouble counting/grouping the results of an inner join I have two tables 1) results_dump: Which has two columns, 'email' and 'result' (the result value can be either "open" or "bounce") 2) all_data: Which has three columns, 'email', 'full_name' and 'address' The first goal is to query the...
elong
1

votes
3

answer
2.3k

Views

TSQL SELECT DISTINCT

I have looked at many examples but i cannot figure this out. Might be too simple for my tired brain. I have a table, I need to select all columns, with one distinct. ID CAT PRODUCT ======================= 1 21 Product1 2 21 Product2 3 23 Product2 4 24 Product3 5 24 Product5 6 25...
user1695981
1

votes
2

answer
216

Views

SQL Server - SELECT all values in table without having them in the GROUP BY

So this question might have already been answered, but it's a bit difficult to search for or even to ask it. I want to query a table and filter it in a number of ways. In the example below, I want to figure out who the first employee was for each dept. Example Table Below Table A employee_id | de...
Mundo Calderon
1

votes
1

answer
73

Views

SQL: Aggregate different months from same table

Lets say I have a table with sales at dates for products. Alas, I cant format a table in here, therefore as code: table1: Product|Date|Sales -------|----|----- ProdA |1.1.|100 ProdB |1.1.| 50 ProdC |1.1.| 75 ProdA |2.1.|110 ProdB |2.1.| 60 ProdC |2.1.| 60 .... |... |... I need a new table wi...
Oliver
1

votes
2

answer
654

Views

Prevent duplicates in mysql group by with join statement

I've got a problem that can't be new, but I can't figure out how to get the answer I want. It is probably something simple that I'm missing Using mysql 5.5, I have 2 tables, 'referrals' and 'status'. I want to count referrals that have been cancelled, grouped by appt_date: SELECT SUM(1) AS count, ap...
ehymel
1

votes
3

answer
80

Views

How to query the latest item in a feed table

There is a table called client_application with data something like this; Id user_id name creationDate --------------------------------------- 1 5 name1 date 2 4 name2 date 3 98 name3 date And also a table called application_status which holds the...
Fatih Donmez
1

votes
3

answer
6.7k

Views

select list of albums of artist and display for every album tracks count which have requested artist and album in list

I need to make complicated query. I have table: CREATE TABLE IF NOT EXISTS tracks ( id INTEGER PRIMARY KEY, path TEXT, title TEXT, artist TEXT, album TEXT, genre TEXT, duration INTEGER, rating INTEGER ); Sory for dirty title of question but i don't understand how to explain it more shortly. Questio...
testCoder
1

votes
1

answer
1.1k

Views

Rails / SQL : Group by and sum each day in the last 2 weeks

I'm trying to sum each day in the last two weeks. This code: Invoice.group('date(filled_at)').sum(:lines_price).to_a returns: [["2012-12-15", #], ["2012-12-17", #]] Which is correct but I need it the return the values in the last two weeks even if the value is 0.0. So the result should be: [["2012-1...
Alex
1

votes
1

answer
1.2k

Views

PHP/MYSQL group and count by distinct dates and users

I'm trying to wrap my head around how to get a distinct count of days a user logged in, when the db has each login session stored with a time and date stamp (DATETIME column). EG: USERID | TIME | BOUGHT -------------------------- 4 | 2012-07-16 04:44:52 | 3 4 | 2012-07-16 04:45:52 | 2 5 | 20...
dama_do_bling
1

votes
1

answer
6k

Views

sql server: case statement in group by

I have this query. Select a."AreaBlkType",Case a."AreaBlkType" when 3 then 'Others' else ( case a."CropType" when 1 then 'Oil Palm' when 2 then 'Rubber' else 'Other Crop' end ) end [Crop] from Table1 a group by case a."AreaBlkType" when 3 then 'Others' else ( case a."CropType" when '1' then 'Oil...
1

votes
2

answer
458

Views

Inner join, count and group by to get ratios

I'm hoping this will be a simple question for somebody who knows what they're doing with mysql. I have two tables at the moment; 'tasks' and 'completion'. Tasks: taskID | task_name | desciption | deadline 1 | task 1 | do this | 2012-08-11 2 | task 2 | do that |...
user1530056
1

votes
3

answer
66

Views

Difficult Grouping Query

I am having trouble writing a query to get the results I want. I have the following table: CREATE TABLE [dbo].[inputs]( [iid] [int] IDENTITY(1,1) NOT NULL, [tag_id] [int] NULL, [date_time] [datetime] NULL, [input_raw] [float] NULL, [input_calibrated] [float] NULL, [input_type] [varchar](50) NULL, [...
Mildfire
1

votes
2

answer
122

Views

Select Count From 1 Column Into 2 Columns

I have a table that has a column called type it holds one of two words upvote or downvote I want to group by a.question_id then get 2 columns with a count of each one. select a.*, u.* from answers a left join users u using(user_id) left join vote_history v using(answer_id) where a.question_id = 4 an...
Get Off My Lawn

View additional questions