group-by

MySQL GROUP BY behavior

Given the following table 'foo' ID | First Name | Last Name ---------------------------- 67 John Smith ---------------------------- 67 Bill Jacobs What first_name and last_name will the following query return and why? SELECT * FROM foo WHERE ID = 67 GROUP BY ID ...

MySQL select sum with group by gives integer results

I have 2 tables: CREATE TABLE `product_det` ( `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT, `prod_name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `product_det` (`id`,`prod_name`) VALUES (1,'Pepper'), (2,'Salt'), (3,'Sugar'); CREATE TABLE `product_oper` ( `id` bigint(12...

Fuzzy grouping in Postgres

I have a table with contents that look similar to this: id | title ------------ 1 | 5. foo 2 | 5.foo 3 | 5. foo* 4 | bar 5 | bar* 6 | baz 6 | BAZ …and so on. I would like to group by the titles and ignore the extra bits. I know Postgres can do this: SELECT * FROM ( SELECT regexp_replace(title, '[*.]+$', '') AS title FROM t...

MySQL Count values based on multiple columns

I have two tables: a 'userlist' that has a 'grade' and 'userID' columns, and a 'login' table that records each time a user is logged in. The login table has a 'userID' column that is associated with the 'userID'. This login table has a row inserted into it each time a user is logged in, so if a user logs in three times, three rows will b...

Sql query to Group by day

I want to list all sales, and group the sum by day. Sales (saleID INT, amount INT, created DATETIME) Update I am using SQL Server 2005 ...

Is it possible to Update single/multiple columns using "group by".

Hi All, I am running group by on 4 columns of two tables. I have unique ID column in two tables. I want to mark both the tables occurrences column to SINGLE/MULTIPLE based on the 4 columns. Is there any way to update based on the results of group by?. ...

MySQL syntax group by restaurant with cheaper menu

Hi everyone ! Here is my SQL request on MySQL (MySQL: 5.0.51a). I want have a list of restaurant with his cheaper menu: select r.id, rm.id, rm.price, moyenne as note, get_distance_metres('47.2412254', '6.0255656', map_lat, map_lon) AS distance from restaurant_restaurant r LEFT JOIN restaurant_menu rm ON r.id = rm.restaurant_id wh...

Group by in SQLite over one subset of a set based on the elements of another subset of the set...

I have a dataset something like this A | B | C | D | E ----------------------------------------------- 1 | Carrot | <null> | a | 111 2 | Carrot | <null> | b | 222 3 | Carrot | zzz | c | 333 4 | Banana | <null> | a | 444 5 | Banana | ...

Group by selects first occurence of field how can I get the last one?

SELECT user_id, created FROM photo_comments GROUP BY user_id returns user_id created 1 2009-10-20 21:08:22 12 2009-10-20 21:45:00 16 2009-10-28 20:35:30 But that created date of 2009-10-20 for user id 1 is the first entry by user id 1. How can I get the last entry by user id 1? (date should be 2009...

Key comparisons for Linq GroupBy using Default EqualityComparer

I'm trying to do a Linq GroupBy on some objects using an explicit key type. I'm not passing an IEqualityComparer to the GroupBy, so according to the docs: The default equality comparer Default is used to compare keys. It explains the EqualityComparer<T>.Default property like this: The Default property checks whether type T i...

T-SQL: Sorting by date, then grouping?

Let's say I have a database table that looks like this: ID name salary start_date city region ----------- ---------- ----------- ----------------------- ---------- ------ 1 Jason 40420 1994-02-01 00:00:00.000 New York W 2 Robert 14420 1995-01-02 00:00:00.0...

LINQ : understanding and executing Groupby to bring back unique rows?

Hi there, i am trying to do a groupby in linq, basically i have a list ( along list - around 1000 entries) and i wish to groupby Description. The entries are vehicles, so hence there are 50 or so Ford Mondeos My query is pretty simple, no joins (yet :-) ) but it brings back a list including 50 Ford Mondeos, i wanted it to group them s...

Group by named column

I always forget how to do things like this. I have a database table with birthdates and I want to find out how many people have the same age. I'm trying: SELECT TIMESTAMPDIFF( YEAR, birthdate, CURDATE( ) ) AS age, COUNT( age ) FROM person GROUP BY age but get the error Unknown column 'age' in 'field list' How can I do a gr...

LINQ to SQL: GroupBy() and Max() to get the object with latest date

Consider a SQL Server table that's used to store events for auditing. The need is to get only that latest entry for each CustID. We want to get the entire object/row. I am assuming that a GroupBy() will be needed in the query. Here's the query so far: var custsLastAccess = db.CustAccesses .Where(c.AccessReason.Leng...

Grouping by 2 fields in XSL

I have next xml: <page> <document> <id>1001</id> <cur>USD</cur> <date>01.01.2009</date> <amount>10</amount> </document> <document> <id>1001</id> <cur>USD</cur> <date>02.01.2009</date> <amount>15</amount> </document> <document> <id>1001</id> <cur>JPY</cur> <...

SubSonic group by

Hi, I'm doing a group by with subsonic but I'm not getting the results expected. var settings = from setting in setting_query group setting by setting.ColumnName into g select g; var items = settings.ToList().Select(s => s.First()).ToList(); var items2 = settings.Select(s => s.First())...

LINQ - join , group by (multiple fields), group by, select new some fields

Hi, i need select some fields from a table in the query using join, but in the select new statement i dont have acess from the fields of the join table. var query = from p in persistencia.RequisicaoCompraItems join s in persistencia.Suprimentos on p.SuprimentoID equals s.SuprimentoID (i need get fields from this join) group p by ne...

Grouping with SubSonic 3

I have the following table "GroupPriority": Id Group Priority 1 1 0 2 2 0 3 3 0 4 2 1 5 1 1 6 2 2 7 3 1 I would like to group these on "Group", order them by "Priority" and then get the one in each "Group" with the highest Pri...

SQL Distinct Grouping?

Here is my query: SELECT dbo.EmailCampaignTracking.emailOpened, dbo.EmailCampaignTracking.emailUnsubscribed, dbo.EmailCampaignTracking.emailBounced, COUNT(*) FROM dbo.EmailCampaignTracking Group By dbo.EmailCampaignTracking.emailBounced, dbo.EmailCampaignTracking.emailUnsubscribed, dbo.EmailCampaignTracking.emailOpene...

Need some help with SQL GroupBY

I have a two column table as follows: ID Emp ID 1 1 1 2 1 3 1 4 2 2 2 6 2 10 3 1 3 5 4 8 5 2 5 6 I need something like this: ID Emp ID 1 1,2,3,4 2 2,6,10 3 1,5 4 8 5 2,6 Please help :) ...