group-by

mysql: how to collect the array data?

hi all. I have a mysql code that needs to be improved because of my limited knowledge. i am new in php-mysql.I have data arrays at my DB and I want to show data such as: Date Model Qty Name 2010-08-23 boo 2 Steve 2010-08-24 boo 1 Steve 2010-08-25 boo ...

Group DateTime by an arbitrary time interval

I have an IEnumerable of an item class defined like this: public class Item { public DateTime Date { get; private set; } public decimal? Value { get; private set; } public Item(DateTime date, decimal? value) { Date = date; Value = value; } } These items are in a specific time interval (5 minutes for exemple)...

Query to group by maximum depth of PostgreSQL Ltree?

I want to query for all products with the name "Shania Twain", but I want group them only by the tree with the deepest nlevel. Assuming I have a table like the following with the name categories +---------------+---------------+---------------+ |id |name |tree | +---------------+---------------+---------...

LINQ query using join and aggregate functions

Urgh...my LINQ has suddenly taken a turn for the worse!! I have two tables and want to join and perform aggregate functions upon the tables. Firstly, is this possible, I am assuming it is, and secondly, how do I handle the aggregate functions? As part of the same LINQ statement? The SQL statement will look a bit like this: SELECT ...

Subsonic Syntax Question (with GroupBy)

Is there a way to do this : SubSonic.Where filter = new SubSonic.Where(); filter.ColumnName = Region.Columns.Region; filter.Comparison = SubSonic.Comparison.IsNot; filter.ParameterValue = null; SubSonic.Aggregate orderBy = new SubSonic.Aggregate(Region.Columns.RegionName, SubSonic.AggregateFunction.GroupBy); RegionCollection regions =...

Linq GroupBy problem

I need to group data held in a list but can’t hardcode the groupBy clause as it mut be definable by the user. The example shown below works fine with the hardcoded “r.DeviceID” in the GroupBy statement. What I would like to do is to change this so that an end-user can select the field the expression will be applied to. At the moment the...

Group Query with Calculations on Rails 3

Rails 3 problem. I have a Foods table of foods with the following attributes: name calories (per gram) fat (per gram) carbs (per gram) protein (per gram) I then have a LoggedFoods table representing a food that has been eaten at a given time. It has the following attributes: food_id number_of_grams_eaten ate_when (datetime) So th...

How do I yield a pre-unpacked list?

I have a list that is created within an itertools.groupby operation: def yield_unpacked_list(): for key, grp in itertools.groupby(something_to_groupby, key=lambda x: x[0]): subset_of_grp = list(item[2] for item in list(grp)) yield key, subset_of_grp If, for example, subset_of_grp turns out to be [1, 2, 3, 4] and [5...

return count 0 with mysql group by

database table like this ============================ = suburb_id | value = 1 | 2 = 1 | 3 = 2 | 4 = 3 | 5 query is SELECT COUNT(suburb_id) AS total, suburb_id FROM suburbs where suburb_id IN (1,2,3,4) GROUP BY suburb_id however, while I run this query, it doesn't give CO...

Linq grouping based on other groups

I am storing transactions in a table. These transactions have an ID which relates to their parent node in a hierarchy tree. An example of my tree is shown in the image below. I need to allow an end user to retrieve the transaction and group them however they want as they are then written to a file based on the grouping. In the image belo...

Group By and Count using nHibernate

Imagine the following tables : Tag (TagId, Label) Post (PostId, Title, Content) User (UserId, Name) UserPostTag (Id, UserId, PostId, TagId) For a Post, multiple users can add one or more Tag. I want to get, via nHibernate, the tag list for a post, with the count of each Tag. Exemple or result : Tag(id1, label1), 7...

Top ten count using Mysql Query

Hi I am using the following mysql query for grouping referrersource. SELECT referrersource,count(referrersource) as counts FROM request_events where referrersource!='' AND landingpage_collectionid=1 group by referrersource Here, I would like to order by my resultset in ascending order.How can I alter my above query.Any suggectio...

multiple GROUP BY columns into one column

Products are grouped for inspected and pass/fail on about 20 criteria. They want a report that counts how many of each defect an individual group has. Defect* is varchar(3) and is used to identify which criteria failed. The table has 3 columns for defects and I can return them with something like: SELECT GroupID, Defect1, COUNT(...

LINQ to SQL group by with take

I have a table that looks like this: Id GroupId Value and it has about 100 rows How can I return the top 10 rows for value but with no duplicating GroupId? ...

Grouping by date, return row even if no records found

I have a query that groups all entries from a table and groups them by the datetime column. This is all working great: SELECT SUM( `value` ) AS `sum` , DATE(`datetime`) AS `dt`` FROM `entry` WHERE entryid = 85 AND DATETIME BETWEEN '2010-01-01' AND '2010-03-01' GROUP BY `dt` ORDER BY `datetime` The problem is, I need it to return...

ILookup versus IGrouping

ILookup and IGrouping are pretty similar Linq interfaces. Both bound a key to a list of values. The question is what differs these both interface. Does anyone have an example what you can do with one type that you are not able to todo with the other one? When should you use "group by" and when "to lookup"? ...

mysql : temporary, filesort with 2 little tables : how can i improve my query ?

CREATE TABLE tableA ( `key` int(11) NOT NULL, `value` int(11) NOT NULL, UNIQUE KEY Akeyvalue (`key`,`value`), KEY Avalue (`value`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE tableB ( `key` int(11) NOT NULL, `value` int(11) NOT NULL, UNIQUE KEY Bkeyvalue (`key`,`value`), KEY Bvalue (`value`) ) ENGINE=InnoDB DEFAULT CHARSET=l...

Count of total groups required. Group by cluased query in MySql

Hi; I want the count of a total groups. I have following query. I want how many groups this query returned based on web_id. Lets suppose if it grouped under 4 groups. I need count 4. select * from web_details where redirected = false group by web_id I hope my question is understood. ...

Return all row having a maximum value

Hi! I'm using entity framework to connect the database. I've a table(Let's call it "File") that haves several fields: ID, Version, XYZ Primarky key is based on ID AND Version. so I can have several line with the same ID but different version(and inversly). The question is: How can I, with a LAMBDA expression, ask my Entity Framewor...

"column XYZ must be in group by" --> "cannot group by aggregate column"

Hi, i have trouble with following sql-statement (trimmed): SELECT nr, (CASE WHEN SUM(vkdtab.amount*liter)<>0 AND jjjjmm BETWEEN 201001 and 201009 THEN SUM(net)/SUM(vkdtab.amount*liter) ELSE 0 END) as return FROM tab GROUP BY 1,2,3 It should give me the amount/liter of items in a special timeframe, but I...