aggregate

SQL - Counting and Summarizing (in ranges) the number of instances of a given transaction

I've got an Occurrences table that contains one row for each time a user took an action. A user take an action multiple times per day. It looks like this: Date Username ------ -------- 1/1/9 User1 1/1/9 User1 1/1/9 User2 1/2/9 User1 1/2/9 User3 1/3/9 User1 1/3/9 User1 1/3/9 User1 1/3/9 User2...

How to rewrite several independent LINQ queries into single one using Aggregate() ?

I have next very non-optimized code: void Summarize(IEnumerable<Section> sections) { this.DateBegin = sections.Select(s => s.Date).Min(); this.DateEnd = sections.Select(s => s.Date).Max(); this.Income = sections.Where(s => s.IsIncome).Sum(r => r.Amount); this.ExpenditureBank = sections.Where(s => s.IsExpenditureBank).Sum...

How to Optimize this SQL Query?

I have 3 tables: CRSTasks (ID,parentID) CRSTaskReceivers (ID,tskID,receiverID) UserNames (id,name) ...relation between CRSTasks and CRSTaskReceivers one-to-many between UserNames and CRSTaskReceivers one-to-one tasks ID parent 1 null 10 1 50 1 taskReceivers id taskID receiverID 1 1 ...

Any reason for GROUP BY clause without aggregation function?

I'm (thoroughly) learning SQL at the moment and came across the GROUP BY clause. GROUP BY aggregates or groups the resultset according to the argument(s) you give it. If you use this clause in a query you can then perform aggregate functions on the resultset to find statistical information on the resultset like finding averages (AVG()) ...

How to select N records for M group in MySQL?

This article elaborates how to select N records each group: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ The best solution for this kind of job is: set @num := 0, @type := ''; select type, variety, price, @num := if(@type = type, @num + 1, 1) as row_number, @type := type as du...

Delayed reference setting in Java

I am developping a CORBA like inter object protocol that would support object aggregate transmission. When objects of an aggregate are serialized, they may have references to objets serialized later. These are forward references. A simple example is a circular list. If each object has a reference to the previously serialized object, th...

linq to sql with multiple aggregators

Hello everybody! I want to translate this simple SQL statement to linq-to-sql: SELECT SUM(field1), SUM(field2) FROM TableName The closest linq-to-sql is (i think) using the group by clause: from tbl in TableName group tbl by (1) into g select new{ value1= g.Sum(p => p.field1), value2 = g.Sum(p => p.field2)}; As this does not produ...

Aggregate functions with a left outer join in LINQ to Entities

I've been looking through related LINQ questions here trying to figure this one out, but I'm having some trouble converting a SQL query of mine to the equivalent LINQ to Entities version. select companies.CommpanyName, job.Position, count(offers.jobID) As Offered, job.Openings, job.Filled from jobs left outer...

IPhone Aggregate iTunes reporting tool

Is there a tool that will aggregate all the rerpots that Apple provides on sales of apps? Apple's reporting tool is very poor and hard to use without manually pulling data into Excel each period. ...

LINQ JOIN + GROUP BY + SUM

Hey there, I have two LINQ statements that I would like to make into one, but for the life of me I can't get it to work. I can't get the grouping to work in the first statement. It complains that the TotalBuy and TotalSell properties aren't there, although doesn't complain about AmountTC and AmountAUD. This should be simple. Any thou...

Report Builder 2.0 or Oracle string aggregation

In Report Builder 2.0, I'm trying to create a table with data like the following: Person | StrID's ------------------- Jim | a, b, c, d Mary | h, k Sue | l, m, p, z The problem is that my Oracle SQL query returns data in the following format: Person | StrID -------------- Jim | a Jim | b Jim | c Jim | d Mary | h...

Calculating average age in Django

I need to find out average age of items (groupped by various criteria, but it does not seem to be the issue). However I fail to find how to effectively create aggregation over DateTimeField using Django (on top of MySQL). Pure Item.objects.all.aggregate(Avg('created')) seems to produce absolutely bogus values (eg. 20081988007238.133) an...

MySQL getting MAX of previous WHERE condition

I have a table: id | score | date bob | 40 | 2010-1-1 bob | 70 | 2010-1-15 sue | 55 | 2010-1-1 sue | 80 | 2010-2-1 I want to query for either the score for a user on a specific date OR, if no score exists for that user on that date,return the score from the most recent date for that user. Is there a way to do this w...

Aggregating across columns in Django

I'm trying to figure out if there's a way to do a somewhat-complex aggregation in Django using its ORM, or if I'm going to have to use extra() to stick in some raw SQL. Here are my object models (stripped to show just the essentials): class Submission(Models.model) favorite_of = models.ManyToManyField(User, related_name="favorite_sub...

Saving a select count(*) value to an integer (SQL Server)

Hi everyone, I'm having some trouble with this statement, owing no doubt to my ignorance of what is returned from this select statement: declare @myInt as INT set @myInt = (select COUNT(*) from myTable as count) if(@myInt <> 0) begin print 'there's something in the table' end There are records in myTable, but when I run the code...

SQL Server 2000: Ideas for performing concatenation aggregation subquery

i have a query that retruns rows that i want, e.g. QuestionID QuestionTitle UpVotes DownVotes ========== ============= ======= ========= 2142075 Win32: Cre... 0 0 2232727 Win32: How... 2 0 1870139 Wondows Ae... 12 0 Now i want to have a column returned that con...

How do I get the values of the lastest entries grouped by an attributes using Django ORM?

I have a report model looking a bit like this: class Report(models.Model): date = models.DateField() quantity = models.IntegerField() product_name = models.TextField() I know I can get the last entry for the last year for one product this way: Report.objects.filter(date__year=2009, product_name="corn").order_by("-date")[0...

How do I get the highest sum per day for last X days?

Hi All, This is probably a easy one, but for the life of me I can't seem to figure it out. Here is my table: Date User Amount ---------- ----- ------ 01/01/2010 User1 2 01/01/2010 User2 2 01/01/2010 User1 4 01/01/2010 User2 1 01/02/2010 User2 2 01/02/2010 User1 2 01/02/2010 User2 4 01/02/2010...

SQL: How do I return zeroes where there is nothing to aggregate across?

Hi What I would like ask is best illustrated by an example, so bear with me. Suppose I have the following table: TypeID Gender Count 1 M 10 1 F 3 1 F 6 3 M 11 3 M 8 I would like to aggregate this for every possible combination of TypeID and Gender...

How to do Linq aggregates when there might be an empty set?

I have a Linq collection of Things, where Thing has an Amount (decimal) property. I'm trying to do an aggregate on this for a certain subset of Things: var total = myThings.Sum(t => t.Amount); and that works nicely. But then I added a condition that left me with no Things in the result: var total = myThings.Where(t => t.OtherProper...