group-by

How to select the latest version of answers given by each answerer in MySQL?

There is a scenario. There is an ask-and-answer website. An answerer can modify his answer, and the history of modification is saved on the server. Be default, only the latest version of each answer is displayed. select * from answers where questionid='$questionid' group by answerer_id So I can group all answers by answerer, then I ...

Aggregating SQL rows with precedence

I have a table full of items from different sources. Some of the sources might have the same location (in my example, different BBC news feeds would be different sources, but they all come from the BBC). Each item has a "unique" ID which can be used to identify it among others from the same location. This means that items relating to the...

SQL merge table and group by

I'm not very good in SQL stuff, so I hope someone can explain this issue to me (and help me solve it) I have a table; ++++++++++++++++++++ | ID | Minutes | ++++++++++++++++++++ | 1012 | 15 | | 1012 | 25 | | 1015 | 45 | | 1016 | 10 | | 1016 | 50 | ++++++++++++++++++++ And I want to achieve, that ...

LINQ Group By Multiple fields -Syntax help

What is the correction needed for example 2 inorder to group by multiple columns Example 1 var query = from cm in cust group cm by new { cm.Customer, cm.OrderDate } into cms select new { Key1 = cms.Key.Customer,Key2=cms.Key.OrderDate,Count=cms.Count() }; Example 2 (incorrect) var q...

Aggregating data by timespan in MySQL

Basically I want is to aggregate some values in a table according to a timespan. What I do is, I take snapshots of a system every 15 minutes and I want to be able to draw some graph over a long period. Since the graphs get really confusing if too many points are shown (besides getting really slow to render) I want to reduce the number o...

how to use sql to connect strings from group condition

view v1 a1 a2 --------- 1 a 1 b 1 c 2 d 3 e 3 f view v2 b1 b2 -------- 1 a;b;c 2 d 3 e;f how to using sql change the v1 to v2 using a1 group to connect a2 ...

group by SKU with count

I have a table with parts in it: parts (partID, sku, ....) The SKU looks like: ABC1232 ABC1332 DSE234 XYZ322 XYZ332 etc... I need to group by manufacturer, so I have to get a substring of the SKU, taking the first 3 characters and then grouping them together and getting a count of them. So the resulting output needs to look like: ...

Linq grouping .include("Table") returning null on Table

I have a linq query that is grouping by answers by QuestionGroup. I need to have the table AssessmentQuestionsReference load so that i can bind to it in my WPF app. var groupedAnswers = from a in App.ents.AssessmentAnswers.Include("AssessmentQuestions") where a.Organisations.OrganisationID == App.selectedOrga...

MYSQL delete all results having count(*)=1

I have a table taged with two fields sesskey (varchar32 , index) and products (int11), now I have to delete all rows that having group by sesskey count(*) = 1. I'm trying a fews methods but all fails. Example: delete from taged where sesskey in (select sesskey from taged group by sesskey having count(*) = 1) The sesskey field could not...

XSLT 1.0 Group By

I have the following XML data: <?xml version="1.0" encoding="iso-8859-1"?> <results> <result> <MemberFirstName>JOHN</MemberFirstName> <MemberLastName>DOE</MemberLastName> <MemberLogin>1</MemberLogin> <Store>0180</Store> </result> <result> <MemberFirstName>JANE</MemberFirstName> <MemberLastName>D...

How to do this GROUP BY query in Django's ORM with annotate and aggregate

I don't really have groked how to translate GROUP BY and HAVING to Django's QuerySet.annotate and QuerySet.aggregate. I'm trying to translate this SQL query into ORM speak SELECT EXTRACT(year FROM pub_date) as year, EXTRACT(month from pub_date) as month, COUNT(*) as article_count FROM articles_article GROUP BY year,month; which output...

LINQ to Entities Group By Driving Me Crazy

I have a LINQ query written in VB.Net that uses Joins to return records from a relational database. I am trying to add a GROUP BY statement, but whichever way I cut it, I either get an error or Visual Studio Intellisense does not allow my format. This is what my query looks like without the GroupBy: Public Function GetBadges(ByVal ser...

MySQL GROUP BY date when using datetime

In MySQL, let's say I have a table with a column called 'actionTime' declared as a 'datetime' (YYYY-MM-DD HH:MM:SS). Is there an easy way to use "GROUP BY actionTime" but only use the 'date' part of the 'datetime'? Thanks ...

Perform an aggregation on a DataTable with Linq in vb.net

Given a datatable, I wish to output an IEnumerable type (dictionary(of T) would be perfect, otherwise a datatable would be acceptable) that provides an aggregation of the data within the datatable. In SQL I would write the query as such: select groupByColumn, sum(someNumber) from myTable group by groupByColumn In VB.NET the closest ...

SELECT DISTINCT values after a JOIN

I have 3 tables: Vehicle: vehicle_id, vehicle_type 1, motorcycle 2, car 3, van Owners: person_id, vehicle_id, date_bought 1, 1, 2009 1, 2, 2008 2, 3, 2009 2, 1, 2005 I want to display a list of all vehicle names. If the person_id = 1, date_bought should also be returned. So I thought I would start with this: SELECT * FROM vehicles ...

How do I get a linq to sql group by query into the asp.net mvc view?

Sorry for the newbie question, but I have the following query that groups parking spaces by their garage, but I can't figure out how to iterate the data in the view. I guess I should strongly type the view but am a newbie and having lots of problems figuring this out. Any help would be appreciated. Public Function FindAllSpaces() ...

Unable to group after left join

Here is my query (from a trigger): UPDATE QuoteItemsGroupFeature SET Cost = (QuoteItemsGroup.BaseCost + QuoteItemsGroup.AccumulatedCost + ISNULL(SUM(ParentQuoteItemsGroupFeature.Cost), 0)) * INSERTED.Amount FROM QuoteItemsGroupFeature INNER JOIN INSERTED ON QuoteItemsGroupFeature.QuoteItemsGroupFeatureId = INSERTED.QuoteIt...

Grouping a list of of entities which has a sublist in c#

I have a set of Entities which basically has this structure. {Stats Name="<Product name> (en)" TotalResources="10" ..} {DayStats Date="2009-12-10" TotalResources="5"} {DayStats Date="2009-12-11" TotalResources="5"} {Stats} {Stats Name="<Product name> (us)" TotalResources="10" ..} {DayStats Date="2009-12-10" TotalResources="5...

Add total to group.

I have a Order Details table. What I want to do is create 1 query to show OrderNumber | SKU | QTY | Price | Line Total | 1 SKU1 1 10.00 10.00 1 ---- 0 0.00 10.00 2 SKU1 2 10.00 20.00 2 SKU2 3 1.50 4.50 2 ---- 0 0.00 ...

sql statement to retrieve a list of results (from other table) for a given record?

I have the following schema: TABLE bands -> band_id -> property1 -> property2 -> ... TABLE tracks -> band_id -> track_id The tracks table typically maps a single band_id onto multiple track_ids (there are multiple tracks for each band). My goal is to build, for the bands that match certain conditions (in terms of property1, property...