FAQ Database Discussion Community


SQL Server Group Results into Period Relative to Date

sql,sql-server,group-by
I'm looking to groups average scores from a query into each day of date. I'm aware that you can get the week of day etc. part of a date with something like: 'Week ' + CAST(DATEPART(wk, s.date_taken) AS (varchar(2)) Week This is not what I am after though. I filter...

filtering a dataframe after groupby in pandas

python,pandas,group-by
I have the following dataframe: In [4]: df Out[4]: Symbol Date Strike C/P Bid Ask 0 GS 6/15/2015 200 c 5 72 1 GS 6/15/2015 200 p 5 72 2 GS 6/15/2015 210 c 15 0 3 GS 6/15/2015 210 p 15 54 4 GS 7/15/2015 200 c 20 50...

Sort within group with linq

c#,linq,sorting,group-by
I have a list of objects. I want to order by Date, and then by TransParentType and then by TransType. Then I want to group by TransParentType and within each group place a specific item (if it exists) at the bottom of the group (withdrawal: specific). Sample Data: Date TransParentType...

Group By in AngularJS

javascript,angularjs,database,group-by
Hi I need to output a table which is like this from the code below table. I just can't do the groupBY name. would like this view to look like Shareholder | Preferred Stock | Common Stock | Options | Percentage | |-------------+-----------------+--------------+---------+------------| | Robert | 35 | 20 |...

How to formulate query to show all courses taken by a person

mysql,group-by,left-join,right-join
I'm having trouble formulating a MySQL query correctly. Everything I've tried doesn't give me what's needed, or gives a syntax error. I have three tables: Clients, Courses, and CoursesForClients. The Clients table just has basic coordinates for a person: ID, Name, Address, email, etc. +----------+-----------------------------+------+ | ClientID | Name |...

group by is not working in postgreSQL

sql,postgresql,group-by
I want group my data by createdAt but it is not working I don't know why... Only group by id which is useless for me. This is working: SELECT "id", "createdAt", "updatedAt" FROM "tables" AS "Table" WHERE "Table"."createdAt" BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04' GROUP BY "id"; This is what...

Python Pandas Adding conditions on cells with duplicate values

python,pandas,group-by
This is a followup to my previous question propagating values over non-unique (duplicate) cells in pandas I have a DataFrame: import pandas as pd df = pd.DataFrame({'First': ['Sam', 'Greg', 'Steve', 'Sam', 'Jill', 'Bill', 'Nod', 'Mallory', 'Ping', 'Lamar'], 'Last': ['Stevens', 'Hamcunning', 'Strange', 'Stevens', 'Vargas', 'Simon', 'Purple', 'Green', 'Simon', 'Simon'], 'Address': ['112...

mongodb distinct of groupby multiple keys

mongodb,group-by,aggregate-functions
I'm trying to query mongoDB to fetch data aggregation (group, match, last). Here is my document: { "_id" : 1, "from" : "a", "to" : "b", "message" : "a to b", "createdAt" : ISODate("2015-06-06T16:42:32.789Z"), "updatedAt" : ISODate("2015-06-06T16:42:32.789Z") } { "_id" : 2, "from" : "a", "to" : "c", "message" :...

SUM case returns value without GROUP BY

mysql,sql,group-by,sum,case
When I add SUM around my case select, it returns the summed value without the GROUP BY. The query I am using, without the SUM, is the following SELECT CASE WHEN subscription_types.type = 'Succes lidmaatschap' THEN 7 ELSE 8 END FROM subscription_used INNER JOIN training_sessions ON training_sessions.id = subscription_used.training_session_id INNER...

sqlite3 compound index usage for LIKE and GROUP BY

sql,sqlite3,group-by,sql-like
I can't seem to get sqlite3 to not use a TEMP B-TREE with a compound index for a query with a LIKE clause and a GROUP BY clause. Seems to only use the index for the WHERE clause. I've tried making the index with all combinations with COLLATE NOCASE in...

SQL access Group By and Sum

sql,sql-server,ms-access,group-by
I'm having an issue with MS Access with my SQL statement. I have companies and teams, and they each have a balance of money. (Company1 can have team 1,2,3,4 and Company2 can have team 1,2,3,4,5. Though Comapny1 team1 is not the same as Company2 team1!) But I have a ton...

Pandas group-by conditional filtering

python,pandas,group-by
I have a DataFrame: import pandas as pd df = pd.DataFrame({'First': ['Sam', 'Greg', 'Steve', 'Sam', 'Jill', 'Bill', 'Nod', 'Mallory', 'Ping', 'Lamar'], 'Last': ['Stevens', 'Hamcunning', 'Strange', 'Stevens', 'Vargas', 'Simon', 'Purple', 'Green', 'Simon', 'Simon'], 'Address': ['112 Fake St', '13 Crest St', '14 Main St', '112 Fake St', '2 Morningwood', '7 Cotton Dr',...

SQL query to select the latest records with a distinct subject

sql,sql-server,group-by,subquery,aggregate-functions
I am using SQL Server and have a table set up like below: | id | subject | content | moreContent | modified | | 1 | subj1 | aaaa | aaaaaaaaaaa | 03/03/2015 | | 2 | subj1 | bbbb | aaaaaaaaaaa | 03/05/2015 | | 3 | subj2...

GROUP BY resulting in undesirable results

mysql,group-by
In the following query I'm pulling a list of applicants on a specific job. No matter what I GROUP BY, it either gives me the same user but with each different application data or I get 4 rows returned for each. I've just begun to get into relational database design...

SQL Update with a Group by Statement not working

sql-server,join,group-by,sum,sql-update
I've got two temp tables. In table @due_cte I have a series of Customer_Ids, followed by payment amounts collected throughout various points in the code. The @work table has a lot of various columns including hist_amt_due. What I want to do is update @work with the amt_due from @due_cte but...

Oracle Sql Query : Error Query on SUM()

sql,oracle,group-by
i want sum() some column. but does'nt work. it's error ORA-00937 please help me. Query SELECT MB.COUNTRY_CODE, /* Error ORA-00937 This line */ MB.OPERATOR_NAME AS COUNTRY_NAME, C.PMN_CODE AS OPERATOR_NAME, (C.RANG_START / 1024) AS RANG_START, (C.RANG_END / 1024) AS RANG_END, COUNT(*) AS TRANSACTION_BY_OPER, '0' AS TERMINATE_CHARGE, SUM(C.RATE * (COUNT(*))) AS TRANSIT_CHARGE...

3 Table JOIN with group by doesn't work

mysql,sql,join,group-by
I have 3 tables: CREATE TABLE data ( id VARCHAR(30) NOT NULL PRIMARY KEY, cat VARCHAR(30), name VARCHAR(30) ) ENGINE=InnoDB; CREATE TABLE sales_data ( id VARCHAR(30) NOT NULL, sold date, sale_s SMALLINT(5) ) ENGINE=InnoDB; CREATE TABLE expenses ( id VARCHAR(30) NOT NULL, date_exp date, exp DECIMAL(5,3) ) ENGINE=InnoDB; and need...

Group by custom function in T-SQL

sql-server,tsql,sql-server-2008-r2,group-by
I have a table of people in which there may be duplicates. My goal is to return a list of possible duplicates so that we can combine them into a new person. I want to group by first_name and last_name, obviously. However, if both person records have a defined birth_date...

MySQL Multiple Conditions on Group By / Having Clause

php,mysql,count,group-by,having-clause
I have three tables that are all inter-related with the following structure. ModuleCategory Table: +------------------+----------------+------------+ | ModuleCategoryID | ModuleCategory | RequireAll | +------------------+----------------+------------+ | 90 | Cat A | YES | | 91 | Cat B | NO | +------------------+----------------+------------+ ModuleCategorySkill Table: +------------------+---------+ | ModuleCategoryID | SkillID | +------------------+---------+ |...

Capping values after a trigger level in a different variable _after GroupBy

pandas,triggers,group-by
There was an elegant answer to a question almost like this provided by EdChum. The difference between that question and this is that now the capping needs to be applied to data that had had "GroupBy" performed. Original Data: Symbol DTE Spot Strike Vol AAPL 30.00 100.00 80.00 14.58 AAPL...

SELECT columns different from GROUP BY columns

mysql,oracle,group-by,sql-order-by,aggregate-functions
Having this database schema (just for illustration purpose) [articles (id_article, title)] [articles_tags (id_tag, id_article)] [tags (id_tag, name)] using MySQL it's possible to do: SELECT a.title, COUNT(at.id_tag) tag_count FROM articles a JOIN articles_tags at ON a.id_article = at.id_article JOIN tags t ON t.id_tag = at.id_tag GROUP BY a.id_article ORDER BY tag_count...

SQL use MAX() function select field not in Group By

sql,ms-access,group-by,max
I want to find the most recent entry for a unique combination of two fields in my table. Employee_Number | Cap_Id | Score | Date_Added I want to find the most recent 'Date_Added' for a group of Employee_Number and Cap_Id but also show the 'Score' entered for that group. I...

group by and union in oracle

sql,oracle,group-by
I would like to union 2 queries but facing an error in oracle. select count(*) as faultCount, COMP_IDENTIFIER from CORDYS_NCB_LOG where AUDIT_CONTEXT='FAULT' union select count(*) as responseCount, COMP_IDENTIFIER from CORDYS_NCB_LOG where AUDIT_CONTEXT='RESPONSE' group by COMP_IDENTIFIER order by responseCount; Two queries run perfectly individually.but when using union,it says ORA-00904: "RESPONSECOUNT": invalid...

Grouping by similar categoies over time sql

sql-server,sql-server-2008,group-by,group,aggregates
I looked around for awhile, but couldn't find anything. I have a table that looks like this: DATE | Shift | Parts Used 1/1/15 1:15.....1........1 1/1/15 2:06.....1........2 1/1/15 3:45.....1........3 1/1/15 7:33.....2........1 1/1/15 8:14.....2........2 1/1/15 9:00.....2........3 1/1/15 23:01....1........1 1/1/15 23:55....1........2 I would like to group by each individual shift. UNFORTUNATELY shift...

XSLT for-each-group group-by variable

xml,xslt,group-by
I am trying to achieve a more flexible groub-by statement for my XSLT, using variable (depending of variable value, there is different grouping needed): ....... <xsl:param name="rows"/> <xsl:variable name="groupBy"> <xsl:choose> <xsl:when test="$report_type='first_type'"> <xsl:value-of select="concat(date,ccy)"/> </xsl:when> <xsl:otherwise> <xsl:value-of select="concat(type,ccy)"/> </xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:for-each-group select="$rows"...

Oracle Query combining Two tables

sql,oracle,group-by
I have two tables. I want to combine this tables to get the results like below: Table_1 RegNo Class_id Name Address ------------------------------------------------ ABC/R/13-14-1 1 Name1 Address1 ABC/R/13-14-2 2 Name1 Address1 ABC/R/2014-15-1 1 Name1 Address1 ABC/R/2014-15-2 3 Name1 Address1 ABC/R/13-14-3 1 Name1 Address1 ------------------------------------------------ Table_2 Class_id Class ---------------------- 1 IA 2...

group by access seperate column

sql,ms-access,select,group-by
I'm very new to SQL and have the following (seemingly simple) database query. I have a table containing all timetracking information for a set of employees: date employeeId timeIn timeOut 01/01/20 1355 12:00 4:00 01/01/20 1355 4:30 6:00 01/01/15 1234 9:00 12:00 02/01/15 5555 5:00 6:00 02/01/15 1111 1:00 4:00...

Select the latest 3 records for each ID in a table

sql,sqlite,group-by,sql-order-by,greatest-n-per-group
I have a table with a composite primary key (ID, Date) like below. +------+------------+-------+ | ID | Date | Value | +------+------------+-------+ | 1 | 1433419200 | 15 | | 1 | 1433332800 | 23 | | 1 | 1433246400 | 41 | | 1 | 1433160000 | 55 |...

Select rows from MySQL and grouping use MAX and MIN

mysql,sql,group-by
I have the following table called 'ArchiveTable': +---------+-----------------+---------+-----------------+--------------+------------------+ | maxtemp | maxtemptime | mintemp | mintemptime | minwindchill | minwindchilltime | +---------+-----------------+---------+-----------------+--------------+------------------+ | 27.9 | 3/17/2015 16:55 | 25.8 | 3/17/2015 19:00 | 25.8 | 3/17/2015 19:00 | +---------+-----------------+---------+-----------------+--------------+------------------+ | 25.7 | 3/17/2015 19:05 | 19.3 | 3/18/2015 9:05 |...

propagating values over non-unique (duplicate) cells in pandas

python,pandas,group-by
I have the following dataframe import pandas as pd df=pd.DataFrame({'Players': [ 'Sam', 'Greg', 'Steve', 'Sam', 'Jill', 'Bill', 'Nod', 'Mallory', 'Ping', 'Lamar'], 'Address': ['112 Fake St','13 Crest St','14 Main St','112 Fake St','2 Morningwood','7 Cotton Dr','14 Main St','20 Main St','7 Cotton Dr','7 Cotton Dr'], 'Status': ['Infected','','Dead','','','','','','','Infected'], }) print(df) and I want to...

Results of SQL grouping not as intended

sql,oracle,group-by
Intro Details: I have an issue with a query for Oracle SQL Developer using client 12c. I've researched several other questions on SO as well as searched Google for an answer to this, but ultimately the answers to all have been to include all columns without aggregate functions in the...

Spark: Counting co-occurrence - Algoritm for efficient multi-pass filtering of huge collections

algorithm,scala,group-by,apache-spark,filtering
There is a table with two columns books and readers of these books, where books and readers are book and reader IDs, respectively : books readers 1: 1 30 2: 2 10 3: 3 20 4: 1 20 5: 1 10 6: 2 30 Record book = 1, reader =...

Python Pandas : group by in group by and average?

python,pandas,group-by,mean
I have a df like this: cluster org time 1 a 8 1 a 6 2 h 34 1 c 23 2 d 74 3 w 6 I would like to calculate the average of time per org per cluster. Expected result: cluster mean(time) 1 15 ((8+6/2)+23)/2 2 54 (74+34)/2...

SQL find references with single occurences

sql,group-by,subquery,aggregate
I am trying to find a pure SQL solution for the following problem: If I sell paint and paintbrushes, and keep a record of which colors are sold like this: select OrderNumber, Product, Product_Type, Qty from Sales +-------------+------------+--------------+-----+ | OrderNumber | Product | Product_Type | Qty | +-------------+------------+--------------+-----+ | 0001...

aggregation condition in case when

mysql,sql,group-by,case,amazon-redshift
I have a dataset with a structure similar to the one bellow fruit, value apple, 234 apple, 2341 pear, 3233 grape, 323 pear, 3234 grap 1234 I am trying to find a count of a range of the numbers that are in the bottom 10% of the range by performing...

Iterating properly through groupby object

python,pandas,group-by
There is a specific problem with iterating properly through a pandas groupby object and I do not yet understand the underlying dynamics good enough to solve my problem. Below, there is a short sample code to simulate my problem: import pandas as pd from pandas import * import random rand...

MySQL - GROUP_CONCAT if value is not a substring

mysql,group-by,group-concat
I have a column called "Permissions" in my table. The permissions are strings which can be: "r","w","x","rw","wx","rwx","xwr" etc. Please note the order of characters in the string is not fixed. I want to GROUP_CONCAT() on the "Permissions" column of my table. However this causes very large strings. Example: "r","wr","wx" group...

Having Trouble with multiple “groupby” with a variable and a category (binned data)

python,group-by
df.dtypes Close float64 eqId int64 date object IntDate int64 expiry int64 delta int64 ivMid float64 conf float64 Skew float64 psc float64 vol_B category dtype: object gb = df.groupby([df['vol_B'],df['expiry']]) gb.describe() I get a long error message with the final line being AttributeError: 'Categorical' object has no attribute 'flags' When i perform...

SQL: Select new columns based on group order

sql,sql-server,group-by,sql-order-by,group
I went through many questions on this site but wasn’t able to find solution. I’ve got a table: Date GroupID CHANNEL 24/02/2015 1 A 26/02/2015 1 B 27/02/2015 1 C 21/03/2015 2 D 20/02/2015 3 E 25/02/2015 3 D 28/02/2015 4 C 04/03/2015 5 B 05/03/2015 5 E 10/03/2015 5...

MySQL Group By - Get row associated with max() amount

mysql,group-by,max
What I am trying to get is the row contents containing the highest price. I am not sure if I need to self-join, or use a having statement. Most of the attempts I have tried netted me no returned rows. Effectively I want to say "WHERE PRICE IS MAX". I...

Django group by hour

python,django,orm,group-by
I have the following model in Django. class StoreVideoEventSummary(models.Model): Customer = models.ForeignKey(GlobalCustomerDirectory, null=True, db_column='CustomerID', blank=True, db_index=True) Store = models.ForeignKey(Store, null=True, db_column='StoreID', blank=True, related_name="VideoEventSummary") Timestamp = models.DateTimeField(null=True, blank=True, db_index=True) PeopleCount = models.IntegerField(null=True, blank=True) I would like to find out the number of people entering the store each hour. To achieve this,...

When trying to call an object with get() within group_by and mutate, it brings up the entire object and not the grouped object. How do I fix this?

r,get,group-by,dplyr,mutated
Here is my code: data(iris) spec<-names(iris[1:4]) iris$Size<-factor(ifelse(iris$Sepal.Length>5,"A","B")) for(i in spec){ attach(iris) output<-iris %>% group_by(Size)%>% mutate( out=mean(get(i))) detach(iris) } The for loop is written around some graphing and report writing that uses object 'i' in various parts. I am using dplyr and plyr. Sepal.Length Sepal.Width Petal.Length Petal.Width Species Size out 1...

Write a query to count different kind of records in single line result

sql,sql-server,sql-server-2008-r2,group-by,sql-server-2012
I have a table with 2 columns TimeStamp and ID in MS SQLSERVER TimeStamp ID 2015-05-20 1 2015-05-20 2 2015-05-20 1 2015-05-21 1 2015-05-21 2 2015-05-21 2 2015-05-21 1 My requirement is to calculate number of records for every Id according to date. Requirement: Date No of records for Id=1...

oracle group by returning more that one row of the same values

sql,oracle,group-by
My query is returning the same values using a group by. Can anyone explain this to me why it is like that or what i am doing wrong . QUERY: SELECT ID, LAST_DAY(EXPDATE) EXPDATE, SUM(BASEUNITS) AS BASEUNITS, SUM(BONUSUNITS) AS BONUSUNITS FROM TABLE WHERE ID= '10' GROUP BY ID, EXPDATE RESULTS...

Select data from three table in sql

sql,sql-server,join,group-by
I have three table Like Student : Sid, SName, SEmail Fees_Type : Fid, FName, FPrice StudentFees : Sid(FK from Student),Fid(FK from Fees_Type), FDate Data of Each Table : Student : SID |SName | SEmail 1 | ABC | [email protected] 2 | XYZ | [email protected] Fees_Type: Fid | FName | FPrice...

Create a new column by grouping on type, new column's value is entire group's column value

string,python-2.7,pandas,group-by,aggregate
I have a pandas dataframe that essentially looks like this: type item string 1 0 aa 1 1 bb 1 2 cc 2 0 dd 2 1 ee 2 2 ff I want to somehow create a new column 'newstring' based off of the group's 'string' column type item string...

R - Grouping Dates and Summarizing Data for repeat purchase

r,group-by,datediff,date-difference
I am trying to do analysis on the repeat purchase behavior of the customers. My goal is two-fold: Eliminate all the customer item zip combination that occurs only once (eliminate the non-repeating ones) For the records that repeat, I want to summarize the same where I need to get the...

MySQL Two Table Min Value and Group By

mysql,group-by,subquery
I have two tables, products, and products_description Products: Product_ID Product_Price 1 10 2 20 3 10 4 20 5 10 6 20 7 10 8 10 9 10 10 10 Products_Description: Product_ID Product_Name 1 Pants - Black 2 Pants - Black 3 Pants - White 4 Pants - White 5...

SQL Server MAX and GROUP BY not playing nicely together

sql-server,tsql,group-by,max,aggregate-functions
I have a T-SQL query of the form: SELECT f.fizz_name, b.buzz_version, fu.foo_name FROM fizz f INNER JOIN buzz b ON f.fizz_id = b.fizz_id INNER JOIN foo fu ON b.buzz_id = fu.buzz_id WHERE f.bar LIKE 'YES' When I run this query I get the following results: fizz_name buzz_version foo_name ==================================== Gamma...

Mysql how to reduce rows by max datetime and group by non-uniqe id

mysql,datetime,join,group-by
I am having a severe case of brain flatulence. Keeping it simple, I have 3 tables: Orders, Statuses, XrefOrdersStatuses I've setup an SQLFiddle (SQLFiddle Here) with simplified schema and some redacted row data. What I need to get are Orders with their most recent XrefOrdersStatuses (joined with Statuses). I can...

python pandas: Simplest way to perform group by and extract count of unique entities?

python,pandas,count,group-by,unique
my df: nr,name 1,sam 2,sam 1,mar 1,sam 2,tom 2,jack 1,mar How can I group by 'nr' and count distinct names in 'name' column? this must be a very easy command in all languages like mysql(groupby and distinct command) but I cannot find this in pandas. Can anybody help?...

Select particular result randomly from a table for a certain partition

sql,database,group-by,partition-by
I want to select record corresponding to 'B' whenever there are duplicates for a name. If there's no duplicate I want to display the record. Refer to the sample table [TableInfo]. Please help me with the SQL query. TableInfo Name Type Value ------------------------ Name1 A 5 Name1 B 10 Name1...

Python Pandas two conditional dataframe groupby running sort

python,pandas,group-by,dataframes
I'm looking for a way to run a two conditional pandas DataFrame groupby method. I have many logs to parse and I have the following single condition groupby method, but is there a way to have a two conditional groupby method? DF[DF['Feature Enabled'] == 1].groupby([’Feature Active'])[['Value1','Value2']].mean() Is there a way...

SUM() Function not Working in sqLite

sqlite,select,group-by,sum
We have a table that is like this: Customer ItemNumber Item_Name Qty FOCQty C1 I1 ABC 10 2 C2 I2 BCA 12 2 C3 I1 ABC 3 3 C4 I2 BCA 05 1 But we want it to be like this: ItemNumber Item_Name Qty FOCQty I1 ABC 13 5 I2...

SQL Server Group By with Group Concat

sql-server,group-by,group-concat
I have a table like this Hospital Insurance PatientCount H1 I1 1 H1 I1 2 H2 I1 1 H2 I2 1 Want to group this table by insurance as, Hospital Insurance PatientCount H1,H2 I1 4 H2 I2 1 Tried using select stuff((select ', ' + Hospital from Insurances where (InsuranceName...

LINQ query string[] group by multiple anonymous columns

c#,arrays,string,linq,group-by
I have a delimited file I am reading into a string array (file has no headers) and then trying to parse with a LINQ query. I want to group by multiple anonymous columns (using the array indexes) and then sum one of the fields. For instance, say I have a...

Using GROUP BY clause on nvarchar data type in T-SQL Query

sql-server,group-by
I am using SQL SERVER 2014. I have a query that stands like this: SELECT [PMSConfirmationNumber],[AR ACCOUNT NAME],sum([FOC NET AMOUNT]) AS [FOC_NET AMOUNT] FROM ARACCOUNT WHERE [PMSCONFIRMATIONNUMBER] = '81827436-1' Group by [PMSConfirmationNumber],[AR ACCOUNT NAME],[FOC NET AMOUNT] However, the PMSCONFIRMATIONNUMBER field being an NVARCHAR data type, I'm having the following output:...

How to select records corresponding to the first row in each group where no concept of minimum exists?

mysql,group-by
My table follows the following structure +--------+---------+---------------------+--------------------+ | userId | loginId | tstamp | action | +--------+---------+---------------------+--------------------+ | 134088 | NE78MEZ | 2014-10-31 13:59:33 | pageview | | 134088 | NE78MEZ | 2014-10-31 13:59:53 | pageview | | 134088 | NE78MEZ | 2014-10-31 14:00:26 | pageview | | 134088 |...

selecting a column value based on a different column value after applying Groupby

python,group-by,dataframes
i am able to get this to work, but not after I apply a groupby. in this example I simply want to have the last column contain the lowest value from column x. I have popuymated the df with a column called yminx with is what I would like my...

How to add numbers to grouped rows in postgresql group by clause

postgresql,group-by,row
I have a PostgreSQL table, containing duplicate values in a text column. It looks like this: gid, capt 1, foo 2, foo 3, bar 4, bar 5, bar 6, buz 7, buz I need to add a column with distinct numbers for each value in a group, so my table...

Do not include select columns in group by

sql,postgresql,group-by,subquery
Here are my tables(including only relevant columns) Table: carts address_id - integer Table: addresses name - varchar phone - varchar Table: orders order_number - integer (this is the foreign key for cart table) I want to fetch phone number of the customers who have ordered only once, so I constructed...

Display mongodb multiple group by query results in a nested format

mongodb,group-by,nested,aggregate,multiple-columns
Given the following dataset: [ { "account_id" : "1111" "task_id" : "aaaa", "workweek" : "20", "hours": "18" }, { "account_id" : "1111" "task_id" : "aaaa", "workweek" : "20", "hours": "12" }, { "account_id" : "1111" "task_id" : "aaaa", "workweek" : "21", "hours": "10" }, { "account_id" : "1111" "task_id" :...

Why is count(column_name) behaving like this?

mysql,sql,database,group-by
Consider the relation account (customer, balance) where customer is a primary key and there are no null values. I would like to rank customers according to decreasing balance. (The customer with the largest balance gets rank 1. Ties are not broke but ranks are skipped: if exactly two customers have...

MySQL: Find out a specific record from a group

mysql,join,group-by
In the customer table, it has customer_ID, name, sex, age, representative_id. I want to find out the youngest male and female customer for a certain representative. I have used SELECT *,MIN(Age) FROM customer GROUP BY Rep_ID, sex; But it returns the first record of each group. How can I choose...

R: Create a column with values based on grouping specific rows

r,group-by,data.frame
Here is a sample data frame, ID <- c(101,102,103,201,202,203,301,302,303,401,402,403) Point_A <- c(10,20,30,40,50,60,70,80,90,100,110,120) df <- data.frame(ID,Point_A) ID Point_A 1 101 10 2 102 20 3 103 30 4 201 40 5 202 50 6 203 60 7 301 70 8 302 80 9 303 90 10 401 100 11 402 110...

How can we merge the results of two queries have group by and two conditions

sql,sql-server,group-by
I have these two tables: (games) and (rounds) table: I would like to get the game id's, there round count, and the count of how many rounds that player1 wins. I created two sql queries like this: but I want to get all results in one table. How can I...

Oracle SQL Group By if

sql,oracle,group-by
In my application I log the file opening in the following table: TESTID SITE LATEST_READ READ_COUNT FILE_ORIGIN_ID ------------- ---------- ----------- ---------- -------------- File1 |Site1 |02/05/13 | 2| 1 File1 |Site2 |22/01/14 | 3| 2 File2 |Site1 |02/06/14 | 8| 0 File3 |Site1 |19/09/14 | 17| 0 File4 |Site2 |19/09/14 |...

oracle duplicate columns to comma seperated string

oracle,group-by
I have duplicated columns in my table I think I should go with group by to get single instance but need a value in another column of that duplicated record for exp: username colour age erick blue 20 erick red 20 dennis green 24 I need a query give result...

Group by and DateAdd and simple computations SQL Server

sql-server,group-by,dateadd
Here is my table and query Table 1 AsOfDate Value 2015-06-06 11:08:21.247 5 2015-06-06 12:08:21.247 7 2015-06-07 10:08:21.247 8 2015-06-08 11:08:21.247 8 2015-06-08 10:09:21.247 9 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, [AsOfDate])) as DateOnly ,Min([Value]) FROM Table1 group by DateOnly I am getting an error message "Invalid column name 'DateOnly'." What...

mysql: order -> limit -> sum… possible?

mysql,group-by,sum,sql-order-by,sql-limit
i am loosing it over the following problem: i have a table with participants and points. each participant can have up to 11 point entries of which i only want the sum of the top 6. in this example lets say we want the top 2 of 3 +----+---------------+--------+ |...

LINQ many to many relationship - get a list of grouped objects

c#,linq,group-by,many-to-many,left-join
I have a bit of a complicated one (for me anyway): I have 2 entities in a many to many relationship. Projects {Project_Id, ProjectName} Users {User_Id, UserName} Projects-Users {Id, Project_Id, User_id} A project can be assigned to more users. Now I want to retrieve the List of all projects (listed...

How do select row with max(column value) group by another column in SQL?

sql-server-2008,tsql,group-by,sql-order-by
i dont't know how to select row with max column value group by another column. I have T-SQL CREATE PROC GET_USER AS BEGIN SELECT A.USER_ID, B.START_DATE , D.START_DATE, A.FULL_NAME,A.COST_CENTER, F.DEPARTMENT_NAME,G.BU_NAME FROM USERS A INNER JOIN USER_PERSON B ON A.USER_ID=B.USER_ID INNER JOIN TYPE_PERSON C ON C.TYPE_PERSON_ID = B.TYPE_PERSON_ID INNER JOIN USER_TRANSACTION...

Show All and Count Duplicates MySQL

mysql,count,group-by
I have this table: id name 1 AAA 2 BBB 3 BBB 4 BBB 5 AAA 6 CCC How can I get this? id name count 1 AAA 2 2 BBB 3 3 BBB 3 4 BBB 3 5 AAA 2 6 CCC 1 I want to count duplicates(name), getting...

Python groupby by two or more columns

python,loops,dictionary,group-by
Could somebody explain me why i can't get the sum of second index(incoming). I have tried to print the value of variable group after than first iteration it seems that once it has been iterated there are no more values. I took some code from this example. example post products...

Group by date using proc sql in SAS

group-by,sas,date-formatting,proc-sql
I want to sum up all values for the same cost centre per each year in SAS. I tried with Group By function in Proc SQL. PROC SQL CREATE TABLE incident_data AS SELECT Cost_Centre_Dim.Cost_Centre_Name, Sum(Count*Amount) AS TOT, OCC_DATE format= Year4. FROM Incidents, Incident_Type, Cost_Centre_Dim WHERE Incidents.Incident_Code = Incident_Type.Incident_Code AND Incidents.Cost_Centre_ID...

How to populate columns in mysql depending on a condition in data?

mysql,select,group-by,row
I have data in mysql tables in the following format id order_id state time_stamp 1 1 created 1:15 6/2/2015 2 2 created 1:17 6/2/2015 3 1 ack 1:18 6/2/2015 4 1 shipped 1:20 6/2/2015 5 1 delivered 1:25 6/2/2015 6 2 ack 1:25 6/2/2015 7 2 shipped 1:26 6/2/2015 8...

Using Distinct or Group by in SQL . Which one is better and faster? State it [closed]

sql-server,sql-server-2008,group-by,distinct,database-performance
I'm Just using the table which having more than 200 thousand records each.I need to Fetch Unique records by joining those tables .Which keyword would work faster in this case using Distinct or Group by ?.It should be looked from performance wise better and faster one.Kindly suggest me.

Pivot Tables or Group By for Pandas?

python,pandas,count,group-by,pivot-table
I have a hopefully straightforward question that has been giving me a lot of difficulty for the last 3 hours. It should be easy. Here's the challenge. I have a pandas dataframe: +--------------------------+ | Col 'X' Col 'Y' | +--------------------------+ | class 1 cat 1 | | class 2 cat...

Linq Group by / Distinct with Join table

linq,join,group-by,distinct
i plan to join 2 table, and get the distinct value of language column. How should i achieve that in Linq? I try add 'group' but no luck. Besides, i want to select s value too together with r distinct language value. My code: public ActionResult QuestionLink(int Survey_ID) { var...

SQL query to select values grouped by hour(col) and weekday(row) based on the timestamp

mysql,group-by,heatmap,weekday
I have searched SO for this question and found slightly similar posts but was unable to adapt to my needs. I have a database with server requests since forever, each one with a timestamp and i'm trying to come up with a query that allows me to create a heatmatrix...

SQL Select - Group by along with inner join?

sql-server,select,group-by,inner-join,distinct
I'm using the following query: SELECT TOP 12 INVENTABLE.ITEMNUMBER, INVENTABLE.ITEMNAME1, INVENTABLE.ITEMNAME2, INVENTABLE.W_TILBUD, INVENTABLE.COSTPRICE, INVENTABLE.VENDITEMNUMBER, INVENTABLE.A_PRODUCENT, INVENTABLE.GROUP_, INVENTABLE.A_GROSSISTLAGER, INVENTABLE.SupplementaryUnits FROM INVENTRANS INNER JOIN INVENTABLE ON INVENTABLE.ITEMNUMBER=INVENTRANS.ITEMNUMBER WHERE INVENTRANS.ACCOUNT='xxx' AND INVENTABLE.W_VISFORSIDE = 0 AND INVENTABLE.W_VISWWW = 1 AND INVENTABLE.BLOCKED = 0 ORDER BY INVENTRANS.ROWNUMBER DESC This query selects the 12...

Grouping by factor absent in dataset

r,group-by,dplyr
I'm using dplyr to calculate the grouping of a variable [mode], for a series of files. The goal is to build a data frame where you copy (cbind) the results for every file (any ideas on a better data structure welcome). So files are like this: FILE 1: id |mode...

How do you create a boarder in an SSRS Report?

reporting-services,group-by,border,hidden,row-number
I've gotten the hang of creating charts/colors and grouping data but I can't seem to figure out how to outline each grouping in my report. I've got data built for my chart - what I would like to have it appear as -- is like this: I'm not picky on...

How to explicitly state the types when grouping in LINQ?

c#,arrays,linq,group-by
I'm playing around with GroupBy(...) and I realize that, although working, I prefer not to use var. Call me anal but I like to know what type I have when I have it. (Of course, I'm not an ideologue and when the syntax is making ones eyes bleed, I do...

performing math on dataframe variables after groupby in pandas and bringing results back to original dataframe

python,pandas,group-by,dataframes
First the data: df City Date Sex Weight 0 A 6/12/2015 M 185 1 A 6/12/2015 F 120 2 A 7/12/2015 M 210 3 A 7/12/2015 F 105 4 B 6/12/2015 M 225 5 B 6/12/2015 F 155 6 B 6/19/2015 M 167 7 B 6/19/2015 F 121 I am...

SQL COUNT- not a single group group function error

sql,oracle,count,group-by,aggregate-functions
I run the following COUNT(*) query on Oracle DB: select count(*) + (select count(*) from t_diagram) from t_object I get the following error: Not a single group- group function. I understand that using aggregation methods (e.g. SUM, AVG) require a GROUP BY statement. However, how can I add a GROUP...

PostgreSQL Order by not working, it doesn’t returns values according to the order

postgresql,count,group-by,sql-order-by
I have two tables players and scores. On the player table I have two fields, name and id (PK) On the scores I have several fields [id_score(PK),winner and looser are foreign key to the id of the player] id tournament and id match. Players id_players | name ------------+----------- 41 |...

R data.table group by and iterate by two columns

r,group-by,iteration
I am new to R and trying to solve the following problem: There is a table with two columns books and readers of these books, where books and readers are book and reader IDs, respectively : > books = c (1,2,3,1,1,2) > readers = c(30, 10, 20, 20, 10, 30)...

SQL XML path conversion results error

sql,sql-server-2008,group-by,common-table-expression,for-xml-path
Actually I m begineer to SQL XML path as so making me professional, Got a scenario... I have a CTE Function That results as Data Chars NumberOfOccurance 12 1 1 appears (1 ) times 12 2 2 appears (1 ) times xx x x appears (2 ) times and CTE...

Select case ALL when grouping in sql

sql,group-by
I want to select where the case is all data in the column meet the conditions as stated in the pseudo code: select s.Id as Id, SUM(CASE WHEN s.IsArchived=0 THEN 1 ELSE 0 END) as Days, ALL(CASE WHEN s.IsArchived=1) AS IsArchived, -- Pseudo code sum(s.Price) as Price from Stuff s...

Sql group by month and year with rollup

sql,group-by,sql-server-2012
I'm using the code below to list a calculated output for each month of the financial year (Apr14 to Mar15): select datename(month,arvd) Mth, year(arvd) Yr, count(submissionid) n, max(HES/12) HES, cast(count(submissionid) / max(HES/12)*100 as decimal(5,2)) DataCompleteness from PRIcache p left join (select distinct hsite, HES from hsite where hyear=2013) hs on...

SQL select and group records with same words in different order

sql,regex,postgresql,group-by,order
I have the following values in a table: Table Fruits id | name | price ----------------------------------------- 1 | 'APPLE BANANA ORANGE' | 12.00 2 | 'BANANA ORANGE APPLE' | 4.00 3 | 'ORANGE APPLE BANANA' | 10.00 4 | 'LEMON APPLE BANANA ORANGE' | 7.00 5 | 'APPLE LEMON BANANA...

sql group by - display all rows

php,mysql,group-by
I'm working on some basic stat tracking website for some buddies and would like to organize the individual player pages a bit more. My plan is to have separate tables print out based on the date they played so they can see games from specific dates a bit easier. Im...

Group query results by “today”, “in last 7 days”, “in last month” and “older”

mysql,sql,database,group-by,group
I have a simple question related to grouping rows by date with some "narrative" periods. Let's assume that I have very simple table with articles. ID which is PK, title and date. The date column is datetime / timestamp. I would like to group somehow my results so I can...

elastic search sort in aggs by column

sorting,elasticsearch,group-by,order
I am trying to sort in elastic search in aggs, equivalent in mysql "ORDER BY Title ASC/DESC". Here is the index structure: 'body' => array( 'mappings' => array( 'test_type' => array( '_source' => array( 'enabled' => true ), 'properties' => array( 'ProductId' => array( 'type' => 'integer', 'index' => 'not_analyzed'...

Django GROUP BY ForeignKey query

group-by
This is my models.py: class QuestionCategory(models.Model): name = models.CharField(max_length=200) class Question(models.Model): question = models.CharField(max_length=200) answer = models.TextField() category = models.ForeignKey(QuestionCategory) What I want to do is to get the questions and group_by the QuestionCategory So I can display my questions like this: Question_Category (1) Question (1) Answer (1) Question (2)...

I'd like to count the documents with the matching “name” property AND group them by “name” at the same time

node.js,mongoose,group-by
Let's say I have a User collection: schema = mongoose.Schema({ firstName: { type: String, required: true }, ... ... }); module.exports = mongoose.model("User", schema); I would like to write a mongoose query that would count how many users go by the name Mike, Andy, Jerry... In other words, I would...

Include and group JSON by parent model

ruby,json,api,ruby-on-rails-4,group-by
I am currently calling an API to get a publisher's Books. I am then grouping each book into different their respective story Arcs. Everything works great and I am able to retrieve the JSON in my app. But, I'd like to change my current JSON response and remove the JSON...

MySQL Group By depending on other colum value

mysql,group-by
i have table like : word id Foo 1 Foo 1 Foo 2 Foo 2 Foo 1 bar 1 bar 1 bar 4 fio 3 fio 3 fio 4 i want to group by word, but only for the same id, the reslut should be like: word id count Foo...

Plotting multiple time series after a groupby in pandas

python,pandas,group-by,time-series
Suppose I made a groupby on the valgdata DataFrame like below: grouped_valgdata = valgdata.groupby(['news_site','dato_uden_tid']).mean() Now I get this: sentiment news_site dato_uden_tid dr.dk 2015-06-15 54.777183 2015-06-16 54.703167 2015-06-17 54.948775 2015-06-18 54.424881 2015-06-19 53.290554 eb.dk 2015-06-15 53.279251 2015-06-16 53.285643 2015-06-17 53.558753 2015-06-18 52.854750 2015-06-19 54.415988 jp.dk 2015-06-15 56.590428 2015-06-16 55.313752 2015-06-17 53.771377...