group-by

Aggregate functions - getting columns not included in the GROUP BY clause

I have a table which has three fields (item_id, source, and price). (item_id, source, and price) together make up the primary key. item_id source price ------- ------- ----- 1 paris 13 1 london 12 1 newyork 15 2 paris 22 2 london 15 2 newyork...

Linq grouping by nullable datetime and using this as criteria

I am struggling with a nullable datetime column [DateInsp] in an ASP.NET app which uses SubSonic3, Linq, MS SQL Server 2005. I had this all working when the datetime column [DateInsp] did not allow nulls. A new requirement forced me to set the [DateInsp] column to allow nulls and now I am struggling getting this piece of functionality ...

Is it safe to include extra columns in the SELECT list of a SQLite GROUP BY query?

I have a simple SQLite table called "message": sequence INTEGER PRIMARY KEY type TEXT content TEXT I want to get the content of the last message of each type (as determined by its sequence). To my surprise, the following simple query works: SELECT MAX(sequence), type, content FROM message GROUP BY type Surprise, because I know that...

C# 2.0 - Is there any way to do a `GroupBy` with a yielded itterator block?

I'm working with a C# 2.0 app so linq/lambda answers will be no help here. Basically I'm faced with a situation where i need to yield return an object but only if one if it's properties is unique (Group By). For example,..say i have a collection of users and i want a grouped collection based on name (i might have 20 Daves but I'd only ...

collapsing NULL values in Oracle query

I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following: id time_in time_out 1 2009-11-01 1 2009-10-30 2 2008-12-15 2 2009-02-03 I then do an outer query like so: SELECT id, MIN(time_in) AS time_in, MIN(time_out) A...

mysql group by performance tuning

select field1,count(*) from table where $condition group by field1 select field2,count(*) from table where $condition group by field2 Basically that's what I'm doing the job now,is there a way to optimize the performance so that MySQL doesn't need to search two times to group by for the where clause? ...

SQL "GROUP BY" issue

Hi, I'm designing a shopping cart. To circumvent the problem of old invoices showing inaccurate pricing after a product's price gets changed, I moved the price field from the Product table into a ProductPrice table that consists of 3 fields, pid, date and price. pid and date form the primary key for the table. Here's an example of what ...

Odd GROUP BY output DB2 - Results not as expected

If I run the following query: select load_cyc_num , crnt_dnlq_age_cde , sum(cc_min_pymt_amt) as min_pymt , sum(ec_tot_bal) as budget , case when ec_tot_bal > 0 then 'Y' else 'N' end as budget , case when ac_stat_cde in ('A0P','A1P','ARP','A3P') then 'Y' else 'N' end as arngmnt , sum(sn_close_bal) as st_bal from statements where...

SELECT TOP 1 in a many to many query

I have a faily simple many to many schema, which looks like this: What I'm trying to do is select all players with some arbitary conditions and I also want to select their most recent match, if they've played in one. What I've managed to do is: SELECT tblPlayer.PlayerId, tblPlayer.Surname, tblPlayer.Forename, (SELECT TOP 1 tblMatch.Ho...

MAX on columns generated by SUM and GROUP BY

I'm trying to get the MAX on a column which is generated dynamically using the SUM statement. The SUM statement is used together with the 'GROUP by' syntax. This is the original query, however it needs to be modified to work with grouping, sums and of course MAX. SELECT SUM(video_plays) AS total_video_plays FROM `video_statistics`...

How to concatenate multiple rows, where fieldnames are duplicated?

I am using SQL. Here is an example of my table: (There are actually thousands of rows like these, with varying course numbers.) Course No | Meeting Day | Course Name | Instructor 123 | M | English | Smith 123 | W | English | Smith 123 | F | English | Smith I need to...

Linq - 2 tables - Sum

Hi, i have 2 tables: Asiento ******** Id_Asiento integer key Fecha date It_Asiento ********** Id_Asiento integer Forenkey Importe float I wan to do This SQL Query with Linq select Asiento.Id_Asiento, Asiento.Fecha, Sum(It_Asiento.Importe) From Asiento join It_Asiento on Asiento.Id_Asiento = It_Asiento.Id_Asiento and It_Asiento.Im...

Strongly Typing a LINQ Query using multiple keys of complex objects

I can't figure out how to define the object used in my LINQ grouping query to allow them to be strongly type. I've build a grouping query that uses two complex objects as keys. The query works, but I would like to be able to declare the return object type. I have a complex type... Public Class Student Public Name As IndividualsNa...

List, Group multiple instances of an object based on ManyToMany field in Django

Given the following models, I need to return a list of Links for each Place, grouped by Category. class Place(models.Model): name = models.CharField(max_length=100) class Category(models.Model): name = models.CharField(max_length=100) description = models.TextField() class Link(models.Model): name = models.CharField(ma...

SQL not a single-group group function

When I run the following SQL statement: SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN It returns the maximum sum value of downloads by a customer, however if I try to find the social security number that that max value belongs to by adding it to the select statement: SELECT SSN, MAX(SUM(TIME)) FROM downloads GROUP BY SSN I get ...

... where count(col) > 1 ...

I have a table like this: +-----+-----+-------+ | id | fk | value | +-----+-----+-------+ | 0 | 1 | peter | | 1 | 1 | josh | | 3 | 2 | marc | | ... | ... | ... | I'd like now to get all entries which have more than one value. The expected result would be: +-----+-------+ | fk | count | +-----+-------+ | 1 | 2 ...

Linq query across three levels of tables to generate sum

I have a series of tables that are joined with Primary Key/Foreign Key relationships. I have a table of Transactions, and each Transaction references one and only one Product. Each product can exist in one and only one Category: Categories Products Transactions ------------- ------------- ----------------- ...

Ordering and grouping in php/MySQL

I have a table with user_id and lap_time and I'm running the following query: SELECT `Lap`.`id`, `Lap`.`user_id`, `Lap`.`lap_time` FROM `laps` AS `Lap` WHERE `Lap`.`lap_time` < 57370 GROUP BY `Lap`.`user_id` ORDER BY `Lap`.`lap_time` ASC I am trying to get the all the laps that are quicker than X but only unique users. The query ...

SQL select statement string concatenation

Can something like this be done with a select statement: SELECT col1, concat(col2 + ' ') FROM .... GROUP BY col1 I know i can use count(col2) or sum(col2) for integers but is there a function for concatenating if the type is nvarchar or nchar? ...

Insert Group By count results into a table

How do you insert a group by count result into a table? I'm trying to insert a list of names with counts for each. Thanks!! ...