group-by

How to select records grouped by the hour of the day including hours that have no records

I have a table in an Oracle database that contains actions performed by users in one of our systems. It's used for statistical analysis and I need to display the number of actions performed for a given date, grouped by hour of the day. I have a query that does that fine, however, it does not display the hours of the day that contain no ...

cloning object on linq grouping - copy constructor substitute

Hi all, I have collection of custom objects(assets) which I want to group with LINQ. Custom object has standard properties like id, name and cost property. When grouping I want to calculate cost for each group, so I'm using little trick like this: from a in assets group a by a.AssetId into ga select new Asset() { ...

MySQl GROUP BY and ORDER BY not doing what I want

I want to sort my results before grouping them, but I can't seem to get it done. I have this query: SELECT DISTINCT product.id, product_number_value.att_value AS product_number, color_number_value.option_code AS color_number, size_value.option_code AS size_code, size_value.option_position AS size_position FROM product INNER JOIN...

MySQL group by consecutive appearances

Hi all, I would like to group query results by consecutive appearances of a column values. Let's say I have a table which lists the winners of a competition for each year as follows: year team_name 2000 AAA 2001 CCC 2002 CCC 2003 BBB 2004 AAA 2005 AAA 2006 AAA I would like a query which outputs: start_end ...

Group-wise-max - hierarchical data query help

Hi, the first part of this problem was solved by some good help on here yesterday, but I've been struggling today to complete the query I need. I am trying to pull multiple columns from 5 joined tables based on some conditions, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key...

Tsql - get entire row information with max and group by

I have a table, with the following columns: PK1 PK2 ID DATE Value flag I do a calculation that involves taking the max value per ID. select id, max(value) from table group by id I want to mark the flag on the rows that I am using. If id and the max(value) correspond to multiple rows flag the one with the...

Mysql: Count records (including zero) per month

Hi, I am trying to count the records in my table and group them per date. My current query looks something like the following: SELECT count(*), MONTH(time) as month, YEAR(time) as year FROM myTable GROUP BY month, year ORDER BY year, month This works, except that I would also like to get a count for months where n...

Trouble with GROUP BY CASE

The following query gives the error "#1241 - Operand should contain 1 column(s)" because of the (Department_Code, Course_Code) line. When I replace that with just (Course_Code) it works. However, that's not what I want SELECT * FROM Classes GROUP BY CASE WHEN (1) THEN Department_Code ELSE CASE WHEN (2) THEN (Department_Code, Cou...

GROUP BY shows all distinct columns

mysql> select * from products; +---------+-------------+-----------+------+ | prod_id | prod_source | prod_type | flag | +---------+-------------+-----------+------+ | 1 | USA | 2 | 0 | | 2 | USA | 2 | 0 | | 3 | USA | 2 | 0 | | 4 | USA | 3 |...

GROUP BY with MAX(DATE)

Hello everyone, I have a table showing trains with their destination and departure time. My objective is to list the latest destination (MAX departure time) for each trains. So for example the table is Train Dest Time 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK 13:00 2 S...

MySQL UPDATE - MAX() GROUP BY

How do I change: SELECT user.postcode, max(postcode.postcode) as postcode FROM user INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode, "%" ) GROUP BY user.postcode into an UPDATE similar to? UPDATE user INNER JOIN postcode ON user.postcode LIKE CONCAT(postcode.postcode, "%") SET user.lat = postcode.lat, user.lng ...

MySQL Refactoring with GROUP BY and HAVING

Hi, I'm doing some refactoring work on PHP/MySQL code and stumbled across an interesting problem, and as of right now, I can't find a proper solution for it. I have this table: CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, userID INT UNSIGNED NOT NULL, timestamp INT UNSIGNED NOT N...

Getting rows from one table and latest 1 row from an associated table, in LINQ...

Ok, I think this question is similar to this one: http://stackoverflow.com/questions/1704821/linq-to-sql-groupby-and-max-to-get-the-object-with-latest-date But I have a table which I want to return a lot of rows from. It's primary key is used as a foreign key in a second table. For each row returned from the first row, I also want to r...

Using SQL function as a group by field in datamapper (ruby)

I'm trying to use a slightly more interesting GROUP BY cause than what seems to be supported out of the box by DataMapper. The query I want to generate looks something like this: SELECT SUM(downloads), SUM(uploads) FROM daily_stats GROUP BY YEARWEEK(date) I'd like to be able to just tell DataMapper something along these lines: DailyS...

How to group by a price confined to a dynamically generated ceiling?

Hello, I have a table of items with several properties but to keep it short, it has property price. I want to group a List<Item> into groups of price ranges. The catch is that the price ranges (ceilings ...) have to be dynamically generated. When the ceilings are static, things work fine (Using LINQ) decimal[] ceilings = new decimal[...

SQL Server exception on column name

When i have a column say "GROUP" TableName.GROUP during select it show me error Incorrect syntax near the keyword 'GROUP' I tried select [TableName.GROUP] from TableName it shows me invalid column name TableName.GROUP i tried select [dbo.TableName.GROUP] from TableName i received the same error invalid column name dbo.Ta...

Help creating a single SELECT statement for SQL Reports

I am trying to create an payment report which lists the sum in each account. The table is in SQL SERVER 2005 and it has the following table [Account]    [Amount]   [Type] 1111     10     C 1111     10     C 1111     15     D 1111     5     D 1112     10     C 1112     15     C 1112  ...

How to group by week of the year dates or day dates that start on Sundays in oracle 10?

Given a table: day_date (form of yyyy-mm-dd) column2 column3 I want to group the data by weeks, and the start of each week goes from Sunday to Saturday, but I want to display the day_date. I have looked around all over, and I'm rusty on my SQL. Thanks! ...

Oracle SQL query count group by timestamp substring

Given a table that has a column of string "timestamps" (yyyyMMddHHmmssSSS format), I want to substring the first 8 characters, and get a count of how many rows have that substring, grouping the results. Sample data... TIMESTAMP 20100802123456123 20100803123456123 20100803123456123 20100803123456123 20100804123456123 20100805123456123 2...

Use LINQ to group by multiple columns and read values of ungrouped columns

I need to group a data table on multiple columns and also read data of un-grouped columns col1 col2 col3 col4 col5 col6 col7 col8 .. col16 Here i am grouping it on col1, col2 ... col6. But facing problems in reading values of col7 ... col16 var groupedRows = from myTable in table.AsEnumerable() group myTable by new ...