group-by

Grouping items from mysql by the day they were posted

So, I'm having a problem building a feed-style list of posts efficiently using PHP and Mysql. I want to output the posts organized and grouped by the day they were posted, showing the date as a header. For example: February 9th ----------- Posts that were posted on Feb 9th Feb 8th ----------------- Posts that were posted on the 8th ...

How can I hierarchically group data using LINQ?

I have some data that has various attributes and I want to hierarchically group that data. For example: public class Data { public string A { get; set; } public string B { get; set; } public string C { get; set; } } I would want this grouped as: A1 - B1 - C1 - C2 - C3 - ... - B2 - ... A2 - B1 - ......

Django Group By relation's data

In Django, I can do value(), and then distinct() to group by. A { Foreign Key B } B { String name } However, is it possible to group using a related object's data? I.e. In the above relation, can I group A by B's name? ...

Linq to SQL equivalent of SUM GROUP BY SQL statement

Hi everybody! I'm having a hard time figuring out how to translate this simple SQL statement to (c#) linq to SQL : SELECT table1.vat, SUM(table1.QTY * table2.FLG01 + table1.QTY * table2.FLG04) FROM table1 inner join table2 on table2.key= table1.key where '2010-02-01' <= table1.trndate and table1.trndate <= '2010-02-28' Group by ta...

How to group by custom types with linq

I have this class public class Item { public Coordinate coordinate { get; set; } ... ... } With Coordinate being define like this: public class Coordinate { public Coordinate(float latitude, float longitude) { Latitude = latitude; Longitude = longitude; } ...

groupby msql query

Hi I have a bunch of ID's. I can assign them once a day. I need to query my database, see if they're any ID's available to hand out (if I didn't hand out all of them in a 24 hour period), and if there's any available, write a new row to the database saying that the ID has been handed out. I also need to know which user held which ID at...

Is this LINQ query with averaging and grouping by hour written efficiently?

This is my first real-world LINQ-to-SQL query. I was wondering if I am making any large, obvious mistakes. I have a medium-large sized (2M+ records, adding 13k a day) table with data, dataTypeID, machineID, and dateStamp. I'd like to get the average, min, and max of data from all machines and of a specific dataType within a 4 hour peri...

How do I use Linq to group a DataTable by multiple rows/columns into a new datatable with duplicate rows concatenated?

God, Linq confuses me... I've found quite a few examples that do similar things to what I need, but I'm still getting all turned around. I have a table (we'll call it UngroupedTable) that looks like this... ID, Val1, Val2 0001, A, 1 0001, B, 2 0002, C, 3 0003, D, 4 0003, D, 5 I want to use Linq to build a table where essentially in Gr...

Is there a better way to write this SQL statement?

Hi, I am getting and calculating some basic order information in my SQL query. I have it working as it should but have been reading about the GROUP BY SQL Clause. I am wondering if the following SQL statement would benefit from GROUP BY and if it would be more efficient to use it? Thanks! SELECT orders.billerID, orders.invoiceDate, o...

LINQ JOIN + GROUP BY + SUM

Hey there, I have two LINQ statements that I would like to make into one, but for the life of me I can't get it to work. I can't get the grouping to work in the first statement. It complains that the TotalBuy and TotalSell properties aren't there, although doesn't complain about AmountTC and AmountAUD. This should be simple. Any thou...

Firebird group by retrieve extra data

I have a table with 6 columns: id, a, b, c, d, e. id is primary key. I am trying to retrieve distinct a, b, c, max(d) for that group, and e that is present in the same row as max(d) is (column "id" is not relevant for my query). I tried this query: SELECT a, b, c, MAX(d), e FROM tablename GROUP BY a, b, c; but it gives me "Invalid e...

Android: Distinct and GroupBy in ContentResolver

What would be a sensible way to add DISTINCT and/or GROUPBY to ContentResolver- based queries. Right now I have to create custom URI for each special case. Is there a better way? (I still program for 1.5 as lowest common denominator) ...

Django group / aggregation

Hi. I have following structure with example data: id season_id title 1 1 Intro 2 1 Second part 3 1 Third part 4 4 Other intro 5 4 Other second part (don't ask why), where season_id is always point to id of first episode of season... What i want, to get follo...

Linq2Sql, GroupBy and return specific object

Hi! I'm have a list of keywords; pizza (10:13 PM) milk (10:12 PM) pizza (10:11 PM) pizza (10:10 PM) milk (10:09 PM) beans (10:08 PM) corn (10:07 PM) juice (10:06 PM) foo (10:05 PM) I want to group them like this (top 5, notice foo is not in the list): Pizza (3) (10:13 PM) Milk (2) (10:12 PM) Beans (1) (10:08 PM) Corn (1) (10:07 PM...

Using list alias in WHERE or GROUP BY clause

Hello, I'm writing a stored procedure containing SQL Select queries with some functions on some columns and a GROUP BY clause. Obviously, I have to repeat these functions in the GROUP BY, which dramatically decreases code readability and increases redundancy. So far, an alternative I have found was to use a derived query so I can acces...

Agregate SQL query with maximun number of rows to take into account

My head is hurting trying to figure out a SQLquery. I have a table of vote data with team_id, ip_address and date_voted fields and I need to return a count of votes for each team_id but only count the first 10 rows per IP address in any 24 hour period. Any help will be greatly appreciated and may stop my head from pounding! ...

How to use group by in xslt

Hi All, I have a xml that has so many elements and most of that contain attributes.. for some of the attributes values are same so I need to group them and generate diff xml. I/p Ex: <TestNode> <ABC1 value="10.7" format="$" /> <ABC2 value="10.5" format="$" /> <ABC3 value="20" format="Rs" /> <ABC4 value="50" format="Rs" /> <ABC5 val...

Problem joining tables where joined table needs to be ordered before grouping

I have a scenario, which is seemingly simple on paper, but I'm having trouble getting to work as desired in practice. I have two tables (only relevant columns presented): | Thread +---------- | ThreadID | Post +---------- | PostID | ThreadID | Posted (Datetime) Now, what I want to do, is to join Thread and Post, grouping by ThreadI...

facebook messages result set like

I'm trying to get my head around with the group by command, basically I'm trying to select all messages of a user, group them by subject then show them - just like how face book does it. The result should have the latest message id, the sender's id, the date, and the total count of the messages in that subject. The message table can lo...

PHP/MySQL group results by column

Hello, in order to keep as few SQL statements as possible, I want to do select set from MySQL: SELECT * FROM products WHERE category IN (10,120,150,500) ORDER BY category,id; Now, I have list of products in following manner: CATEGORY - product 1 - product 2 CATEGORY 2 - product 37 ... What's the best and most efficent way to pr...