group-by

SQL Syntax -- Group By ... Custom Aggregate Functionality?

I've got a situation like this: Table: FunTable ItemKey.....ItemName.....ItemPriceEffectiveDate....ItemPrice 11.....ItemA.....6/6/2009.....$500 12.....ItemA.....6/15/2009.....$550 13.....ItemA.....9/9/2009.....$450 14.....ItemB.....3/9/2009.....$150 15.....ItemB.....9/9/2009.....$350 I need to do the following: Select   ItemName as It...

Why must you GROUP BY the columns in a CASE statement?

I'm trying to run a query in SQL Server 2008 against a table where some of the data was entered inconsistently and I have to handle this. Table data example: OrderID Qty Price MarkedUpTotal 1 10 1.00 11.00 1 -1 1.00 -1.10 1 -1 1.00 1.10 I have to handle the situation where the Qty is ne...

Linq: using StringComparer with GroupBy/Distinct in query syntax

I have this (XLinq) query and was wondering how to convert it to the query syntax: var grouped = doc.Descendants() .GroupBy(t => t.Element(ns + "GroupingAttr").Value, StringComparer.OrdinalIgnoreCase); This is the query syntax without the StringComparer: var grouped = from t in doc.Descendants() group t...

create nested objects in javascript like groupby in C#

IList<Customer> Customers = flat.GroupBy(cust => new { cust.ReferenceNumber, cust.Name, cust.Address }) .Select(c => new Customer() { ReferenceNumber = c.Key.ReferenceNumber, Name = c.Key.Name, Address = c.Key.Address, ...

SQL: How to Group by Custom Year

For simplicities sake, I'll make up a similar example to what I have: Let's say a db has a table of orders with an OrderDate field and a Company field. Then there's a table of Companies and each record has a YearEndingDate (which signifies that the year ends on that date each year, e.g. 6/6). I need to add up all of the orders for eac...

Group by & count function in sqlalchemy

I want a "group by and count" command in sqlalchemy. How can I do this? ...

Grouping result by date in mysql

This SQL statement SELECT `ip`, `when` FROM `metrics` WHERE `vidID` = '1' GROUP BY DATE('when') returns one result, even though multiple are present in the table with different dates. I've tried using DATE_FORMAT as well. Am I doing something stupid? When is a timestamp column with full timestamp, including hours, minutes and seconds...

Multiple groups in LinqToSql

I dont seem to be able to find any evidence of multiple groupby being supported in LinqToSQl on the internet (I should probably buy a book :)) I'd like to be able to do this: var qry = from s in DBContext.Styles join artist in DBContext.Artists on s.ID equals artist.StyleID join track in DBContext.Tr...

MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT emailID, fromEmail, subject, timestamp, MIN(read) as read FROM incomingEmails WHERE toUserID = '$userID' GROUP BY LOWER(fromEmail) ORDER BY timestamp DESC The query almost works as I want it--it s...

C# Linq Group By issue, probably really easy!

Here's my Class that I'm grouping: public class RefundTran : DataObjectBase<RefundTran> { public string ARTranID { get; set; } public decimal Amount { get; set; } public string ARTranTypeCode { get; set; } public int CheckNumber { get; set; } public int CustID { get; set; } public stri...

Linq to objects nested group-by

I have this query working but it is not returning back exactly what I am looking for. I have a collection of: List<TransactionRecord> transactionLog; TransactionRecord simplified looks something like this: class TransactionRecord { public string txSetComments; public string BatchComments; public string TargetComments; pu...

How to minimize the load in queries that need grouping with different invervals?

I'm looking for a best practice advice how to speed up queries and at the same time to minimize the overhead needed to invoke date/mktime functions. To trivialize the problem I'm dealing with the following table layout: CREATE TABLE my_table( id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, important_data INTEGER, date INTEGER);...

MySQL 5: Does it matter what order my GROUP BY fields are in?

Peeps, I have a few aggregate/calculated fields in my MySQL query. My GROUP BY clause is dynamically generated, depending on what options a user selects in a web form. Curious if the order of fields listed in the GROUP BY clause can have any impact on the calculations (things like SUMs, AVERAGEs, etc) Thanks! ...

T-SQL, zero sum for no match on join

Hi All I have two tables, one with all my Branches, and one with all my sales. The sales table also contains a Sales Rep ID, a Branch ID, a month and a year. I need a query that will return the sum of a specific rep's sales for a year, grouped by branch and month, and the query must return 0 if there has been no sales in a branch for ...

PHP/MYSQL query help with grouping results

I have a MYSQL table with 3 fields: CODE, DATE, COUNT. CODE is the ID of the client DATE is the date the entry was added COUNT is the number of credits used by that client on that date I need to be able to know how many credits each client has used on a per month basis. So I need to group the results by client and by year/month. Basic...

SQL Server Group By Query using multiple values

I am new to Microsoft SQL Server and have been frustrated by a GROUP BY query that won't do what I want it to. The table is the following: make model distancefrom distanceto driverid toyota yaris 358.2 368.2 401 toyota yaris 368.2 378.7 103 toyota yaris 378.7 382.2 103 toyota yaris 382.2 392.2 103 to...

How do you group by multiple columns in LINQ TO SQL?

How do you group by multiple columns in LINQ TO SQL? db.Table.GroupBy(a => a.column1.ToString() + a.column2.ToString()) It seems ugly and with poor performance, and I don't even know if it works. Which is the right way to do it? ...

GROUP BY ID range?

Given a data set like this; +-----+---------------------+--------+ | id | date | result | +-----+---------------------+--------+ | 121 | 2009-07-11 13:23:24 | -1 | | 122 | 2009-07-11 13:23:24 | -1 | | 123 | 2009-07-11 13:23:24 | -1 | | 124 | 2009-07-11 13:23:24 | -1 | | 125 | 2009-07-11 13:23:24 | ...

SQL Server GROUP BY error

Here is the query I am trying to run: SELECT TOP 5 PageComment.ID FROM PageComment WHERE PageComment.ParentID IN (SELECT ID FROM ProjectPage) GROUP BY PageComment.ParentID What I want to get as a result, is one comment per project however, this query gives this error: "[Microsoft][SQL Native Client][SQL Server] Column PageCo...

MySQL GROUP BY Problem

Hi all, I have a complex MySQL problem. SELECT * FROM banners, content_id_country, languages WHERE content_id_country.content_id = banners.id AND content_id_country.controller = 'banners' Gives me a long result back. The result is perfect, there is only one problem. Some rows are duplicates, so they have only one field c...