Linq to Objects: does GroupBy preserve order of elements?
Does Enumerable.GroupBy from LINQ to Objects preserve order of elements in the groups? ...
group-by
"Indexing" (aka maintaining a table of) aggregate data in SQL Server 2005
I have a table which maintains performance data of a system, each record is a call made to some important method and consists of the method name, its duration and a token - each request to the system is given a unique token and so all of the records with the same token are the same request, e.g: CallName Duration Token -----------...
MySQL: grab one row from each category, but remove duplicate rows posted in multiple categories
Hi all. I have a database of articles, which are stored in categories. For my homepage, I want to grab an article from each category (I don't care which). However, some articles are crossposted to multiple categories, so they come up twice. I have a table called tblReview with the article fields (reviewID, headline, reviewText) and a ...
SQL group by day, with count
I've got a log table in SQL Server that looks like this: CREATE TABLE [dbo].[RefundProcessLog]( [LogId] [bigint] IDENTITY(1,1) NOT NULL, [LogDate] [datetime] NOT NULL, [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RefundId] [int] NULL, [RefundTypeId] [smallint] NULL, [LogMessage] [varchar](1000) COLLATE S...
Retrieve rows grouped by hour with MySQL
I have a table containing access logs. I want to know how many accesses to resource_id '123' occured in each hour in a 24 hour day. My first thought for retrieving this info is just looping through each hour and querying the table in each loop with something like... and time like '$hour:%', given that the time field holds data in the f...
How to get away with non-grouping field in HAVING clause
When executing in *ONLY_FULL_GROUP_BY* mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query so I don't get the error anymore in *ONLY_FU...
Oracle group by and the empty resulting set
Hi! I have the following SQL problem. Scenario: I have two tables: Change and ChangeTicket. There is a 1:n relationship. One Change has cero or many changeTickets. No change means no changeTickets. A changeTicket has a status (open/closed/...) A changeTicket has a field representing how many minutes took this change. A Change has a co...
C# List - Group By - Without Linq
I have an object: IObject { string Account, decimal Amount } How do I group by Account and Sum the Amount, returning a List without Linq. 2.0 Framework ... that is why no Linq. Here is what I have: ListofObjects = List<IObject>; foreach (var object in objects) { var objectToAdd = new Object(object); ...
How can I get the IDs of the rows that make up a GROUP BY ... HAVING query?
I have the following query that looks for transactions that cancel themselves out from the same customer (Some transactions are negative). SELECT c, ABS(r) magnitude, SUM(r) total, COUNT(*) num FROM table GROUP BY c, magnitude HAVING num > 1 AND total = 0 ORDER BY total The result of this query is the customer id, the magnitude of the...
How do I query for records with multiple values for the last column in a compound GROUP BY clause?
Given data that looks similar to this: +---------+-----------+----------+ | country | city | district | +---------+-----------+----------+ | Japan | Tokyo | 1 | | Japan | Tokyo | 1 | | Japan | Tokyo | 2 | | China | Shanghai | A | | China | Shanghai | A | | China | Shangha...
MySQL % question
I'm working with survey data. Essentially, I want to count total responses for a question for a location, then use that total to create what percentage of all responses each particular response is. I then want to group results by location. An ideal output would be similar to this: Q1 | State | City | % yes| MA | bos |10 no | MA ...
Filling in missing dates using a linq group by date query
I have a Linq query that basically counts how many entries were created on a particular day, which is done by grouping by year, month, day. The problem is that because some days won't have any entries I need to back fill those missing "calendar days" with an entry of 0 count. My guess is that this can probably be done with a Union or so...
Having trouble with SQL COUNT
I'm using MS SQL 2008 and I have a table of statuses (id, name) and a table of items (id, name, statusid, deleted). I want to count the number of items with each status, and have the following query: SELECT status.id, ISNULL(COUNT (items.name), 0) AS 'count' FROM status LEFT OUTER JOIN items ON items.statusid = status.id GROUP BY statu...
SQL to show all entries within a number of categories
Hi, I'm writing an sql query on three tables which finds and displays categories and all the entries within each category For example Category 1 post 1 post 2 post 3 post 4 Category 2 post 5 post 6 Category 3 post 7 post 8 etc I have the categories displaying but can only get one item from each. Can anyone suggest a better approac...
LINQ to DataSet case insensitive group by
I have a data table and I want to perform a case insensitive group by over a column of data table (say Column1 of type string). I observed that normally LINQ to DataSet perform case sensitive comparison. For example if Column1 is having two values say Test and test, after applying group by it returns two seperate rows with values Test an...
has_many and sum named_scope
I have this situation: Stories has many Tasks Tasks have an integer called hours_left I need a named scope to find Stories which all its tasks has more than 0 hours left. Based on this post. I wrote this: class Story has_many :tasks named_scope :uncompleted, { :joins=>["INNER JOIN tasks ON tasks.story_id = stories.id"], ...
How to query Open-high-low-close (OHLC) data from SQL Server
Hi, I'm trying to retrieve data for a Open-high-low-close (OHLC) chart directly from the database, it's the kind of chart you see of stocks. Is this possible, and if, how? I have a table like this (simplified): Date | Price | PriceType A record is created for each day, I will report per month / year, not per day as used for stocks. ...
How to make LEFT JOIN work with grouping/count in MySQL?
I have a query like this: SELECT type.id, type.name, COUNT(*) AS tot FROM page LEFT JOIN type ON page.type=type.id GROUP BY type.id However, this doesn't select all the types: it misses out any types that are not in the page table yet. I just want it to list every type with a number representing how many pages have that type, includin...
xcode core data: how do I get "computed" attributes?
Hello, I have a entity in my core datamodell, "Tours". Tours has a attribute called "creationDate". In my iphone up, I want the tours to be seen in an table view, ordered by creationDate. This works fine. But now I want to go into master/detail. That is, I want to see in the master table view all Year/Month combinations from my tours en...
SQL to produce Top 10 and Other
Imagine I have a table showing the sales of Acme Widgets, and where they were sold. It's fairly easy to produce a report grouping sales by country. It's fairly easy to find the top 10. But what I'd like is to show the top 10, and then have a final row saying Other. E.g., Ctry | Sales ============= GB | 100 US | 80 ES | 60 ... ...