group-by

help me build this query

I want to extract rows of group by rls_id but with latest/recent date SELECT * FROM `tbl_revisions` where `date` in (SELECT MAX(`date`) FROM `tbl_revisions` group by `rls_id`) group by `rls_id` The above query works well but i dont want to use subqeuries .I need some other way around. CREATE TABLE IF NOT EXISTS `tbl_revisions` ( `i...

MySQL / Ruby on Rails - How to "SUM" in a :has_many case

Hi there, I have the following tables: User :has_many Purchases Item :has_many Purchases Item has a column "amount" (can be + or -) and I need to find all Users that have a positive SUM of "Item.amounts" (over all Purchases each one has made). How does this query look like? (I'm not sure how to handle "SUM" correctly, in this c...

Group By and Aggregate function

I want to write an efficient query which returns a list of fruits by type, the lowest price for the type of fruit and the name of the fruit. Right now, I have a query which return me the fruit type and the lowest price for that type (see below). But I am unable to get the name of the cheapest fruit. Any idea how I can achieve that? Than...

Mysql is taking .7 seconds to "copy into tmp table". What is the best way to cache this kind of query?

I have a group-by query that is very fast when it comes to indexing, joining, sending the results etc. Unfortunately, mysql spends 99.6% of its time "copying to tmp table" when I profile it. I am at a loss as to how I can get mysql to perform better on its own. This group by query essentially finds the number of entities for the top 20 ...

Tricky SQL SELECT statement - combine two rows into two columns

My problem: I have a table with a Channel <int> and a Value <float> column, along with a timestamp and a couple of other columns with additional data. Channel is either 1 or 2, and there is either 1 or 2 rows that have everything except channel and value the same. What I'd like to do is select this data into a new form, where the two ch...

MySQL GROUP BY quantity

Hi, all! MySQL table is like this (VoteID is PK): VoteID VoteValue CommentID 1 -1 1 2 -1 1 3 1 1 4 -1 1 5 1 2 6 1 2 7 -1 2 I need a result: CommentID Plus Minus 1 1 3 2 2 1 ...

How to "Group By" with MongoDB

I would like to know if there is anyway I can "group by" my results with MongoDB/Php. This is the code I'm using to display my rows: $count = $col->find(array('order_id' => array('$gt' => '0')))->count(); I need to group them by order_id. Apparently, we cannot do a count() on a group() as find() WORKS: $countfind = $col->find()->co...

Grouping data in memory using Linq To Objects or using native SQL -- WHICH IS FASTER?

I noticed that LINQ to Objects has a GroupBy method. In light of this, I was wondering if anyone can build a case for group list of objects in memory using LINQ vs having SQL Server perform the grouping? ...

mysql query - select distinct value where it has a specific values , group by

I currently have SELECT * FROM bidders WHERE status='0' AND email IS NOT NULL GROUP BY `bid_user` ORDER BY 'bid_price' DESC The problem that I have is that "bid_user" may exist in different rows with different status ( status=1 or status=0). I would like to know if it's possible to select only the * rows where status=0 and whe...

MYSQL Select/Group By: How do I remove a group from the result based on a value of one of it's members?

Consider the table of events below. The OrderID field is a foreign key to an Orders table. OrderID EventType TimeAdded* Processed 1 ... 3 Hours Ago 0 2 ... 5 Minutes Ago 0 1 ... 2 Hours Ago 0 1 ... 1 Hour Ago ...

SQL: Group counts in individual result rows

When I run query number 1: SELECT id, name, COUNT(name) AS count FROM markers WHERE state like 'OR' group by named I get the following results: id name count 14 Alsea 2 76 Applegate Valley 1 3 Ashland 9 64 Beaver 1 26 Bend 1 10 Carlton ...

SQL - aggregate function to get value from same row as MAX()

I have one table with columns channel, value and timestamp, and another table with 7 other columns with various data. I'm joining these two together, and I want to select the maximum value of the value column within an hour, and the timestamp of the corresponding row. This is what I've tried, but it (obviously) doesn't work. SELECT ...

populating a aggregate table

I have a question target table str_week_day end_week_day age_id usage_ratio eq_type ------------ ------------ ------ ----------- ------- 11-Jul-10 17-Jul-10 1.00 0.5 RECEIVER 11-Jul-10 17-Jul-10 2.00 0.5 HUB 18-Jul-10 24-jul-10 1.00 0.5 RECEIVER 18-Jul-10 24-jul-10 2.00 ...

Select most occurring value in MySQL

Hi everyone, I'm looking for a way to select the most occurring value, e.g. the person who posted most for each thread; SELECT MOST_OCCURRING(user_id) FROM thread_posts GROUP BY thread_id Is there a good way to do this? ...

MySQL - Group on similar timestamps

Query: SELECT project_id, COUNT(*) AS count, MIN(date_added) AS date_start, MAX(date_added) AS date_end FROM my_table GROUP BY project_id, TIMESTAMPDIFF(MINUTE, date_added) < 5 WHERE user_id = 1 LIMIT 10 How can I accomplish this? I want to group the items so that no two consecutive items in a group...

Why im getting wrong number of rows when using group by, limit and group_concat in mysql?

Hi everybody, i'm doing a query where i want retrieve some records when i do the query without 'limit' the result returns me the correct number of rows, but when i use limit the result returns me the half of the rows. the query im using is something like this: SELECT WGI_TaxoName.ScientificName, WGI_Locality.Locality, IFNULL(C...

PHP, MySQL: Need to show Parent name only once.. almost there but still a problem

Hi all, I have a parent category that holds all Cars names, denoted by parent_name in table "parent". For each of these parents, there could be any number of car models & they all go in table called "model". Each of these models can have any number of images & refereced via the model_id as the Foreign Key. My task is to show all the Par...

php loops and grouping mysql

Hi There, I am currently adding a purchase history section to payment system, the system is built in PHP (codeigniter framework) and mysql. The idea of the payment history section is that all the customer purchases are grouped together by date, so in the end the HTML would look similar to this, My question is I can get the data out o...

Hibernate - setProjection after Criteria after Criteria

why is this not possible? Criteria crit1 = sess.createCriteria(Criteria1Class.class); Criteria crit2 = crit1.createCriteria("criteria2Class"); crit2.setProjection(Projections.groupProperty("criteria2Property")); List<String> l2 = crit2.list(); If I use this construction I get the error: could not resolve property: criteria2Property...

group by in R, ddply with weighted.mean

Hi, I am trying to do a "group by" - style weighted mean in R. With some basic mean the following code (using the plyr package from Hadley) worked well. ddply(mydf,.(period),mean) If I use the same approach with weighted.mean i get the following error "'x' and 'w' must have the same length" , which I do not understand because the w...