MySQL Group By ordering
Hi all, I have the following table: id time text otheridentifier ------------------------------------------- 1 6 apple 4 2 7 orange 4 3 8 banana 3 4 9 pear 3 5 10 grape 2 What I want to do is select ...
group-by
HQL: group by month
Hi everyone, I'm trying to group by month some rows using HQL, but I'm kind of new to that API and can't seem to get it to work. Here is my code: Criteria query = getHibernateSession().createCriteria(SalesPerformance.class); // summary report is grouped by date query.setProjection(Projections.projectionList().add( ...
Tricky MySQL Query for messaging system in Rails - Please Help
Hi, I'm writing a facebook style messaging system for a Rails App and I'm having trouble selecting the Messages for the inbox (with will_paginate). The messages are organized in threads, in the inbox the most recent message of a thread will appear with a link to it's thread. The thread is organized via a parent_id 1-n relationship with...
Ratio of matching rows per group
I'd like to calculate the ratio of items in a group that fulfil certain criteria out of the total number of items in that group. I've already solved this but am curious to know if my solution is optimal, as the query takes a problematically long time over my large (10m+) dataset. Here is what I have in its simplest form: create table #...
Django - ORM question
Just wondering if it is possible to get a result that I can get using this SQL query with only Django ORM: SELECT * FROM (SELECT DATE_FORMAT(created, "%Y") as dte, sum(1) FROM some_table GROUP BY dte) as analytics; The result is: +------+--------+ | dte | sum(1) | +------+--------+ | 2006 | 20 | | 2007 | 2230 | | 2008 | 49...
mysql select sum group by date
Quick question, I have the following table +-------------+---------------------+ | total | o_date | +-------------+---------------------+ | 35 | 01-11-2009 19:32:44 | | 41.5 | 01-12-2009 22:33:49 | | 61.5 | 01-23-2009 22:08:24 | | 66 | 02-01-2009 22:33:57 | | 22.22 | 02-01-200...
SQL group by problem
I have a query which does a number of joins and has a few criteria in the WHERE clause, and I'm ending up with a result which essentially looks like this: | userId | date | otherData | |--------+------------+------------| | 1 | 2008-01-01 | different | | 1 | 2009-01-01 | info | | 1 | 2010-01-01 | for ...
SQL Sum Column before inserting into #temp
In my SPROC a table named #temp1 contains the following columns: #temp1 (StoreId, StoreDesc, ReservedQty, AvgPrice, QtyOnHand) My question is based on the following query INSERT INTO #temp2 (StoreId, StoreDesc, CommittedQty) (SELECT StoreId, StoreDesc, CASE WHEN ReservedQty > QtyOnHand THEN sum(QtyOnHand * AvgPrice) ELSE su...
Concat groups in SQL Server
If I have a table like this: +------------+ | Id | Value | +------------+ | 1 | 'A' | |------------| | 1 | 'B' | |------------| | 2 | 'C' | +------------+ How can I get a resultset like this: +------------+ | Id | Value | +------------+ | 1 | 'AB' | |------------| | 2 | 'C' | +------------+ I know this is really easy ...
condensing query using SQL
I have a table with 4 things I want... the Name, Price, QTY, and a specific Date There are lots of Entries per date: Name Price Date Twin Private $25 06/02/09 Double $35 06/02/09 Single $20 06/02/09 Twin Private $25 06/03/09 Double $35 06/03/09 Single $20 06/03/09 Twin Private ...
Group by clause work in one environment, but not another
I have an absolutely baffling case in front of me. I have two database environments, A and B, where environment B has been created from a backup of A. I'm running a rather simple query: SELECT customers.customerName (* varchar(100), not null *) ,countries.countryName (* varchar(100), not null *) ,Balance ...
How to group data from temporary table
Hi guys, I have created one temporary table in which corresponding to one fileid which is primary key, I am having 5 rows. I want to get the count as 5. What query do I have to write to get the count corresponding to each fileid? ...
How to retrieve 10 latest DISTINCT IP's from a MySQL table?
Hey guys, I have a table containing pagehit (normalized) data and I need to grab the 10 latest unique ips. I tried to do it like this: SELECT * FROM spy_hits ORDER BY date desc GROUP BY ip LIMIT 10; Which should give me this result: +-----+------------+-----+---------+----+------+------+---------+-------+-------+ | id | date ...
Conditional Group By statement using LINQ
I have what seems to be a fairly simple requirement, but looking around I'm not able to get a simple answer for this. I have looked on MSDN forums, Exper Exchange and nothing substantial was given to me. I have the following LINQ code Dim SummaryLog As IQueryable(Of clPartCountSummary) SummaryLog = From Inventory In db.tblPartCount...
MYSQL shows incorrect rows when using GROUP BY
I have two tables: article('id', 'ticket_id', 'incoming_time', 'to', 'from', 'message') ticket('id', 'queue_id') where tickets represent a thread of emails between support staff and customers, and articles are the individual messages that compose a thread. I'm looking to find the article with the highest incoming time (expressed as ...
SQL Server 2005 error when grouping using subquery.
Using SQL Server 2005 I'm trying to group based on a case statement with a subquery, but I'm getting an error ("Each GROUP BY expression must contain at least one column reference. "). I can work round it quite easily, but can anyone explain the error? I've got a column reference to #header.header. create table #header (header int) crea...
Need help converting query with joins and group by/count to LINQ to SQL
I need help converting this query to linq to sql: select rd.RouteDispatchID, r.RouteNumber, s.ShortDescription Status, rd.DispatchDate, rd.CreationDate CreatedDate, e.FirstName, e.LastName, count(md.MachineDispatchID) NumMachines from dbo.RouteDispatch rd inner join dbo.Route r on rd.RouteID = r.Route...
MySQL - Selecting a Column not in Group By
I'm trying to add features to a preexisting application and I came across a MySQL view something like this: SELECT AVG(table_name.col1), AVG(table_name.col2), AVG(table_name.col3), table_name.personID, table_name.col4 FROM table_name GROUP BY table_name.personID; OK so there's a few aggregate functions. You c...
Oracle SQL Query to Summarize Statistics, using GROUP BY
I have an Oracle table with data that looks like this: ID BATCH STATUS 1 1 0 2 1 0 3 1 1 4 2 0 That is, ID is the primary key, there will be multiple rows for each "batch," and each row will have a status code in the STATUS column. There are a bunch of other columns, but these are the important...
How to speed up "select count(*)" with "group by" and "where"?
How to speed up select count(*) with group by? It's too slow and is used very frequently. I have a big trouble using select count(*) and group by with a table having more than 3,000,000 rows. select object_title,count(*) as hot_num from relations where relation_title='XXXX' group by object_title relation_title, object_title i...