group-by

How to group using XSLT

I'm having trouble grouping a set of nodes. I've found an article that does work with grouping and i have tested it and it works on a small test stylesheet i have I now need to use it in my stylesheet where I only want to select node sets that have a specific value. What I want to do in my stylesheet is select all users who have a use...

mysql query performance help

Hi I have a quite large table storing words contained in email messages mysql> explain t_message_words; +----------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------+------+-----+---------+----------------+ | mwr_key | int(11...

MySQL select using datetime, group by date only

Is is possible to select a datetime field from a MySQL table and group by the date only? I'm trying to output a list of events that happen at multiple times, grouped by the date it happened on. My table/data looks like this: (the timestamp is a datetime field) 1. 2010-03-21 18:00:00 Event1 2. 2010-03-21 18:30:00 Event2 3. 2010-03-30...

count total records after groupBy select

Hello, I have a mysql select query that has a groupBy. I want to count all the records after the group by statement. Is there a way for this directly from mysql ? thanks. ...

What is a SQL statement that can tally up the counts even including the Zeros? (all in 1 statement)

A SQL statement can give a list of the most popular gifts that are sent in a Social application, all the way to the ones that are sent 1, or 2 times, but it won't include the Zeros. I think the same goes for getting the list of the most popular Classes that students are registering for, when the registration process for all students is ...

In SQL, what does Group By mean without Count(*), or Sum(), Max(), avg(), ..., and what are some uses of it?

In SQL, if we use Group By without Count(*) or Sum(), etc, then the result is as follows: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | ...

In SQL, why is "select *, count(*) from sentGifts group by whenSent;" ok, but when "*" and "count(*)" is switched position, then it gives an error?

In SQL, using the table: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | 1 | 2010-04-24 | 123 | 456 | 2010-04-24 01:52:20 | 10...

SQL Server "Group By" for an interesting case

Hello, I have a table like that ID ORDER TEAM TIME IL-1 1 A_Team 11 IL-1 2 A_Team 3 IL-1 3 B_Team 2 IL-1 4 A_Team 1 IL-1 5 A_Team 1 IL-2 1 A_Team 5 IL-2 2 C_Team 3 What I want is grouping the same named teams which are also sequential teams (Which is according t...

Dynamic group by in linq to objects on Silverlight

How can i do dynamic group by in linq to objects in #siverlight client side? i want to do something like this: var x = "empt.Dept"; var groups = from emp in employees group emp by x into g select new { Dept = g.Key, Count = g.Count() }; I tryed also to use reflection and do something like employee.GetType().GetProperty("Dep...

CakePHP and SQL Server 2008, Group By not working

Whenever I do a: $this->Job->find('all', array( 'group' => array('Job.some_field'), 'recursive' => -1 )); I get a : SQL Error: Column 'jobs.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. With MySQL it works fine but with SQL Server 2008 it se...

Specify sorting order for a GROUP BY query to retrieve oldest or newest record for each group

I need to get the most recent record for each device from an upgrade request log table. A device is unique based on a combination of its hardware ID and its MAC address. I have been attempting to do this with GROUP BY but I am not convinced this is safe since it looks like it may be simply returning the "top record" (whatever SQLite or M...

How to use count and group by at the same select statement

Hello, I have an sql select query that has a group by. I want to count all the records after the group by statement. Is there a way for this directly from sql? For example, having a table with users I want to select the different towns and the total number of users select town, count(*) from user group by town I want to have a colum...

Hibernate criteria query using Max() projection on key field and group by foreign primary key

I'm having difficulty representing this query (which works on the database directly) as a criteria query in Hibernate (version 3.2.5): SELECT s.* FROM ftp_status s WHERE (s.datetime,s.connectionid) IN (SELECT MAX(f.datetime), f.connectionid FROM...

How can I get a COUNT(col) ... GROUP BY to use an index?

I've got a table (col1, col2, ...) with an index on (col1, col2, ...). The table has got millions of rows in it, and I want to run a query: SELECT col1, COUNT(col2) WHERE col1 NOT IN (<couple of exclusions>) GROUP BY col1 Unfortunately, this is resulting in a full table scan of the table, which takes upwards of a minute. Is there any...

How to select the first row for each group in MySQL?

In C# it would be like this: table .GroupBy(row => row.SomeColumn) .Select(group => group .OrderBy(row => row.AnotherColumn) .First() ) Linq-To-Sql translates it to the following T-SQL code: SELECT [t3].[AnotherColumn], [t3].[SomeColumn] FROM ( SELECT [t0].[SomeColumn] FROM [Table] AS [t0] GROUP BY ...

can use more than 1 column in MySQL Group BY?

Hi, I want write these SQL Query: CREATE VIEW `uniaverage` AS SELECT `averagegrade`.`mjr`,`averagegrade`.`lev`, AVG(`averagegrade`.`average`) AS `uniAVG` FROM `averagegrade` GROUP BY `averagegrade`.`lev`, `averagegrade`.`mjr`; But MySQL Query Browser give this error: Operand Should Contain 1 column(s) I somewhere read...

Using GROUP BY to fetch rows from SQLiteDatabase and then render them using SimpleCursorAdapter

I want to display total amount of seconds that was spent each day on a project. I want to use SQLiteDatabase to fetch rows using the query below and then use SimpleCursorAdapter to fill a ListView. SELECT projectId, date, sum(endedAt-startedAt) as hours FROM project_reports WHERE startedAt >= 1270098000 AND endedAt <= 1272776399 GROUP ...

querying huge database table takes too much of time in mysql

Hi all, I am running sql queries on a mysql db table that has 110Mn+ unique records for whole day. Problem: Whenever I run any query with "where" clause it takes at least 30-40 mins. Since I want to generate most of data on the next day, I need access to whole db table. Could you please guide me to optimize / restructure the deploymen...

Top x rows and group by (again)

Hello, I know it's a frequent question but I just can't figure it out and the examples I found didn't helped. What I learned, the best strategy is to try to find the top and bottom values of the top range and then select the rest, but implementing is a bit tricky. Example table: id | title | group_id | votes I'd like to get the to...

Why do I need to explicitly specify all columns in a SQL "GROUP BY" clause - why not "GROUP BY *"?

This has always bothered me - why does the GROUP BY clause in a SQL statement require that I include all non-aggregate columns? These columns should be included by default - a kind of "GROUP BY *" - since I can't even run the query unless they're all included. Every column has to either be an aggregate or be specified in the "GROUP BY", ...