Questions tagged [group-by]
4814 questions
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...
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...
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...
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...
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...
-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...
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',...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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:...
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...
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...
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...
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...
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...
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...
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...
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)?...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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 |...
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,
[...
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...