group-by

Get last state of item

In MySQL, I have two tables with a 1:n relationship. Table items has products, whose state is kept in another table, like so : items: id |ref_num|name |... 1 |0001 |product1|... 2 |0002 |product2|... items_states : id|product_id|state_id|date 1 |1 |5 |2010-05-05 10:25:20 2 |1 |9 |2010-05-08 12:3...

Group Number in a LINQ Group by Query

I've been using 101 LINQ Samples to get my feet wet using LINQ. It's been a good first resource, but I can't see an example there of what I currently need. I just need to associate a sequential group number with each group. I have a working solution: var groups = from c in list group c by c.Name into details select new { Name ...

NHibernate: how to express a specific "group by" query with criteria

Question: What are the criteria/projections that can generate a following query? SELECT SUBSTRING(Name, 0, 1) FROM Person GROUP BY SUBSTRING(Name, 0, 1) (Obviously this one is easier with DISTINCT, but I'll need counts later, when I fix this one). My approaches: My main problem here is with constants, because if I use Projecti...

LINQ join and group

How to expand this query: public Dictionary<int, List<TasksInDeal>> FindAllCreatedTasks() { return (from taskInDeal in db.TasksInDeals where taskInDeal.Date > DateTime.Now && taskInDeal.Date < DateTime.Now.AddDays(7) group taskInDeal by taskInDeal.CreatedByUserID into groupedDemoClasses ...

SQL group by -- can merge?

In SQL we could add group by clause -- then do sum, count, or avg of a particular (numeric) column. Is there a way to "merge" / "concatenate" particular column -- for each group? I need this done in one SQL statement ...

C# cast to interface with List and GroupBy

I'm a bit stumped about how to perform the necessary cast in the following: public IList<IMyClass> Foo() { IList<IMyClass> foo = SomeQuery(); var result = foo.GroupBy(x => x.bar).Select(x => new MyClass()).ToList(); // So now I have a List<MyClass> which needs casting as IList<IMyClass> return result; } using an ex...

Simple SQL problem

I can't believe I'm getting so stuck on what seems like such a simple query. I need to get back the User of a Log for a given Project which has the maximum DateLogged value. I've rewritten it a million ways but this is the way which expresses what I want the clearest - SELECT L.User FROM Log AS L WHERE L.Id = 24 GROUP BY L.ProjectId HA...

SQL: Is it possible to 'group by' according to 'like' function's results?

Hello, I am using Oracle SQL and I want to group some different rows that 'like' function results. To elaborate with an example: Let's assume I have a table MESA with one of the columns is a huge string. And I am counting the number of rows matching particular patterns: SELECT m.str, count(*) FROM MESA m WHERE m.str LIKE '%FRUIT%' A...

SQL Group By question - choosing which row gets grouped

I have a table that tracks serial numbers and the number of licenses associated with said serial number. A client can re-license a box, increasing or decreasing the number of users, and they are only billed for the changed amount. The following table structure exists: id - Auto Incrementing ID serial - The serial number numusers - the ...

How to group and count in rails

I am currently trying to create graph statistics for jobs in a PBS. I have a jobs model that has many fields, among them is a "Group" field. I would like to know how many jobs each group has executed. For that, I need the following query: SELECT jobs.`group`, COUNT(`group`) AS 'number_of_jobs' FROM jobs GROUP BY jobs.`group` Which re...

Get the set of grouped values as a list using linq

I am taking a datatable and finding all the rows for a specific key that have fewer than three entries in the table for that key value. I can do this fine and it returns a grouping with the key being the id I want to group on and a list of the datarows that, for each key value, don't exist at least three times. Now I want to get a straig...

Get SUM in GROUP BY with JOIN using MySQL

I have two tables in MySQL 5.1.38. products +----+------------+-------+------------+ | id | name | price | department | +----+------------+-------+------------+ | 1 | Fire Truck | 15.00 | Toys | | 2 | Bike | 75.00 | Toys | | 3 | T-Shirt | 18.00 | Clothes | | 4 | Skirt | 18.00 | Clothes | | 5 | ...

Django aggregate and grouping : code cleanliness problem ...

Hello ! Here is a small problem I am having. 3 very simple models : >>> class Instrument(models.Model): ... name = models.CharField(max_length=100) ... >>> class Musician(models.Model): ... instrument = models.ForeignKey(Instrument) ... >>> class Song(models.Model): ... author = models.ForeignKey(Musician) I would l...

Entity Framework LINQ - Subquery with Group By

Hi guys, I am trying to achieve a query which includes a subquery which itself includes grouping. I based my code from answers to this question The purpose of the code is to perform a simple de-duplication of the 'person' table based on the email address and return the latest person row. var innerQuery = (from p in db.Person ...

I'm having problem with SUM() in MySQL

Hi All, I want to fetch the BalanceAmt of a particular Customer attended by a particular attender. The criteria is that a customer may buy more than one items. Each bought items has been recorded individually. At some cases 'attender' value would be NULL. Here's my query: SELECT Customer, AttendedBy, SUM(BalanceAmt) FROM billing GROU...

Is there a better way to aggregate a dictionary using LINQ?

I am trying to build a dictionary from an enumerable, but I need an aggregator for all potentially duplicate keys. Using ToDictionary() directly was occasionally causing duplicate keys. In this case, I have a bunch of time entries ({ DateTime Date, double Hours }), and if multiple time entries occur on the same day, I want the total ti...

mysql right join with group by problem

I have 2 tables: LandingPages - contain landing pages per campaign. Reports - contain hits and conversion per landing page. I try to do query that bring the sum of hits and conversion per landing page, But i want that if the landing page has not received any hits and conversion (and not show in reports table) then i want that return...

Are these two queries the same - GROUP BY vs. DISTINCT?

These two queries seem to return the same results. Is that coincidental or are they really the same? 1. SELECT t.ItemNumber, (SELECT TOP 1 ItemDescription FROM Transactions WHERE ItemNumber = t.ItemNumber ORDER BY DateCreated DESC) AS ItemDescription FROM Transactions t GROUP BY t.ItemNumber 2. SELECT DISTINCT(t.ItemNumb...

Group by month and year in MySQL

Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format. I am trying to organize a json object into years / months. json to base the query off: { "2009":["August","July","September"], "2010":["January", "February", "October"] } Here is the query I have so far - SE...

Aggregating values into a table type within a GROUP BY query

Suppose you have a table (in Oracle): CREATE TABLE CUSTOMER ( customer_id NUMBER, gender CHAR(1) ); And suppose you have a table type: CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER; Is it possible to write a GROUP BY query such that, for each group, that group's primary key fields are stored in a NUMBER_TABLE_TYPE? For ...