group-by

MySQL + PHP (grouping by date)

I'm trying to output a list of different itmes grouped by the date they were stored in the database (unix timestamp). I'd need help with both MySQL (query) and PHP (output). MySQL table id | subject | time 1 | test1 | 1280278800 2 | test2 | 1280278800 3 | test3 | 1280365200 4 | test4 | 1280451600 5 | test5 | 1280451600 ...

SQL: Any straightforward way to order results FIRST, THEN group by another column?

I see that in SQL, the GROUP BY has to precede ORDER BY expression. Does this imply that ordering is done after grouping discards identical rows/columns? Because I seem to need to order rows by a timestamp column A first, THEN discarding rows with identical value in column A. Not sure how to accomplish this... I am using MySQL 5.1.41 ...

MySQL query to get list of latest updated pages, but only one result for every slug?

I have a table for pages with fields slug (string, not unique) and updated (datetime). I want to retrieve a list of last updated pages based on the updated field, but I only want one result for every slug (since slugs can be re-used across pages in this case). What to do? I tried this: SELECT * FROM `pages` GROUP BY `slug` ORDER BY `up...

Help forming a SQL statement.

I have two tables: attractions and cities. Attractions contains a column called city, which is a reference to the id in the cities table. I want to form a MySQL statement that will show me which cities have the most attractions. I know I can do: SELECT COUNT(*) as `number` FROM `attractions` WHERE `city` = XX ...to get a count o...

How can I select several rows with the Max column value which is grouped by another column?

I want to get the movie, character_name, and Visits of the 4 rows(*) having the max "Visits" value group by movie. My table (D) is like this: movie character_name Visits 1 Owen Lars 1 1 Obi-Wan Kanobi 2 1 Luke Skywalker 3* 2 Princess Leia 2 2 Luke Skywalker 3* 2 R2-D2 3* 3 Jabba t...

[Hibernate] How to group results by intervals?

Hello all, I have a table containing events with a "speed" property. In order to see the statistical distribution of this property, I'd like to group the results by intervals, let's say: [0-49.99km/h] 3 objects [50-100km/h] 13 objects [100-150km/h] 50 objects etc This would let me see that most objects are in a certain interval. Ob...

HQL version of a SQL join with group by

I have two tables, Band and Votes. Band has a name and an id, and Votes has a total_votes column and a foreign key called band_id pointing to band.id. I have lots of votes, saved at various dates. What I want to do is find the maximum value of the total_votes column for each band. The following SQL query works: select b.name,max(v.t...

Limit JOIN query on specific table

Hi there, I'm trying to perform a select query over two tables, one containing, for example, products, and the other containing the colors that can be chosen for those products. Each product has a different number of colors that can be chosen; some have only one color, others can have 20 of them. The scope of the query is to parse a l...

SQL: how to get more information from a GROUP BY statement

Consider the following SQL statement: SELECT invoice.customer_name, invoice.customer_id, invoice.shop_location, sum(invoice.cost) FROM invoice GROUP BY invoice.customer_name, invoice.customer_id, invoice.shop_location What can be done to the statement to identify which rows would be identical to other rows if it weren't for their i...

Using GroupBy, Count and Sum in LINQ Lambda Expressions

I have a collection of boxes with the properties weight, volume and owner. I want to use LINQ to get a summarized list (by owner) of the box information e.g. **Owner, Boxes, Total Weight, Total Volume** Jim, 5, 1430.00, 3.65 George, 2, 37.50, 1.22 Can someone show me how to do this with Lambda expression...

simple Group By

Sorry for this simple question, but i'm missing the forest through the trees. These are my tables (i left the sparepart-table because i'm only looking for two special fk's): I need the distinct rows from tabData which are referenced in the child-table tabDataDetail with fiSparePart=8837 and 8969. The following gives me only that rows ...

Pivot tables without aggregation?

I have a list of items - each item has a title, plus a bunch of attributes, including a date. If I drop the date into the row box of a pivot table, then group it, and drop the title below - so I have Years, Quarters, Created On, Title, I get a nice list of all the titles in my data, grouped by year, quarter and month. But I want to als...

SQL Server joins, groups, and etc. to do simple number crunching

Here's what I'm working with (SQL Server): departments: deptID, deptName students: studID, studName, deptID assignment: studID, courseID, status Students are assigned to a department and the student record holds the department's ID number. The "assignment" is a link between a student and a course (course not shown) that holds a statu...

Group together arbitrary date objects that are within a time range of each other

I want to split the calendar into two-week intervals starting at 2008-May-5, or any arbitrary starting point. So I start with several date objects: import datetime as DT raw = ("2010-08-01", "2010-06-25", "2010-07-01", "2010-07-08") transactions = [(DT.datetime.strptime(datestring, "%Y-%m-%d").date(), ...

GroupBy with multiple groups as a hierarchy

I am using GroupBy create a hierarchical set of groups to use in multiple child grids. Assume I have a query with with 6 columns, a, b, c, d, e, f. Now, I need to group by a, then by b, then by c. and return the entire row in the group of c's. var q = rows.GroupBy(x => x.a) Ok, that's nice. That gives me my group of a's. Next, we ...

Linq: Grouping a list, sorting it and getting top x values?

I have a list: Name Country Value John Ireland 100 Mary UK 200 Peter Germany 300 Bob UK 100 Pat France 400 I need to Group the list by the country, then sort by the summed values DESC then take the top X values e.g. List.TopX(3) would return France 400 UK 300 Germany 300 ...

How to use itertools.groupby when the key value is in the elements of the iterable?

To illustrate, I start with a list of 2-tuples: import itertools import operator raw = [(1, "one"), (2, "two"), (1, "one"), (3, "three"), (2, "two")] for key, grp in itertools.groupby(raw, key=lambda item: item[0]): print key, list(grp).pop()[1] yields: 1 one 2 two 1 one 3 three 2 two In an attempt...

How to group rows in SQL with more than 2 columns are of the same value?

One of the usages of GROUP BY is to group entries where a specified column is of the same value. How can we group entries if 2 or more selected rows have the same value? ...

SQL: GROUP BY records and then get last record from each group ?

I have a table like this: id| name | attendence 1 | Naveed| 1 2 | Naveed| 1 3 | Adil | 1 4 | Adil | 1 I use following query: SELECT * FROM `test` WHERE `attendence`=1 GROUP BY name The result with above query: id| name | attendence 3 | Adil | 1 1 | Naveed | 1 Question: Above result group rows by name but show first row f...

COUNT is grouping rows unexpectedly

Table items keeps track of the different types of items that can be stocked. item_type item_name last_stock 1 cake 2010-08-10 2 fruit 2010-08-07 3 soda 2010-08-07 4 water 2010-08-09 Table individual_items keeps track of each specific item. id item_type 1 ...