aggregate

Django - finding the extreme member of each group

I've been playing around with the new aggregation functionality in the Django ORM, and there's a class of problem I think should be possible, but I can't seem to get it to work. The type of query I'm trying to generate is described here. So, let's say I have the following models - class ContactGroup(models.Model): .... whatever .....

SQL - WHERE AGGREGATE>1

Hi, Imagine I have a db table of Customers containing {id,username,firstname,lastname} If I want to find how many instances there are of different firstnames I can do: select firstname,count(*) from Customers group by 2 order by 1; username | count(*) =================== bob | 1 jeff | 2 adam | 5 H...

Can Rails' Active Record handle SQL aggregate queries?

Hi, Just started learning active record and am wondering how to best retrieve data from multiple tables where an SQL aggregate query is involved. In the following example (from a medical app) I'm looking for the most recent events of various types for each patient (e.g. last visit, last labtest etc). As you can see from the sql query be...

Count(*) vs Count(1)

Hi, just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference for SQL Server 2005 in performance? Or is this just a legacy habit that has been brought forward from days gone past? ...

Tricky SQL-aggregate query with DateTimes

I have the following DataTable (counting hours of work): start | end | time -----------------+------------------+------------------ 2009-05-01 08:00 | 2009-05-01 10:00 | 2009-05-01 02:00 2009-05-02 07:30 | 2009-05-02 11:00 | 2009-05-02 03:30 2009-05-03 23:00 | 2009-05-04 02:00 | 2009-05-03 03:00 There's a ...

SQL JOIN, GROUP BY on three tables to get totals

I've inherited the following DB design. Tables are: customers customerid customernumber invoices invoiceid amount invoicepayments invoicepaymentid invoiceid paymentid payments paymentid customerid amount My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any ...

Aggregate function and other columns

Hello it is possible for a SQL query to return some normal columns and some aggregate ones? like : Col_A | Col_B | SUM 5 6 7 ...

How can i receive aggregate queries for ruby's active record?

Using ruby, camping webframework, activerecord-2.1.1, my db structure is ... create_table :Conf_posts do |t| %w{title body username posttime hit passwd}.each do |col| t.column :"#{col}", :string end end I want sum of each username's hit I have the following code. Post.find :all, :select => "username,sum(hit)", :from => "Co...

Linq Efficiency question - foreach vs aggregates

Which is more efficient? //Option 1 foreach (var q in baseQuery) { m_TotalCashDeposit += q.deposit.Cash m_TotalCheckDeposit += q.deposit.Check m_TotalCashWithdrawal += q.withdraw.Cash m_TotalCheckWithdrawal += q.withdraw.Check } //Option 2 m_TotalCashDeposit = baseQuery.Sum(q => q.deposit.Cash); m_TotalCheckDeposit = baseQuery....

SQL question about counting...

I want to make a query so that I can grab only Locations that have at least 50 Places. I have a table of Locations: Id, City, Country 1, Austin, USA 2, Paris, France And a table of Places connected to Locations by Location_id Id, Name, Details, Location_id 1, The Zoo, blah, 2 2, Big Park, blah, 2 I can join them like so: SELECT p...

Get maximum value of a GROUP BY query without subquery in mySQL

hi, i have some queries which group datasets and count them, e.g. SELECT COUNT(*) FROM `table` GROUP BY `column` now i have the number of rows for which column is the same, so far so good. problem is: how do i get the aggregate (min/max/avg/sum) values for those “grouped” counts. using a subquery sure is the easiest, but i was...

DDD: subclasses & root entities

Hello. I'm new to DDD. Let's say I have the typical entity Car class Car : Entity { public double MaxSpeed { get; set; } public Color Color { get; set; } /* ... */ } This entity, in my domain model, would be the root entity of an Aggregate. Now let's say I specialize cars. I create a Ferrari, and the happy owners of Ferr...

using an aggregate function to narrow my query

I want to do something like this: SELECT locations.id, places.id, scannables.id, SUM(scannables.available) FROM `scannables` INNER JOIN places ON scannables.place_id = places.id INNER JOIN locations ON places.location_id = locations.id WHERE locations.id = 2474 AND scannables.bookdate BETWEEN '2009-08-27' and date_add('2009-08-27', IN...

How to do join with SUM in ActiveRecord

Let's say I have two simple models project t.string :title vote t.references :project t.integer :value When I do loop throuh all projects, I also want to include sum of all votes, because doing projects = Project.all foreach project in projects sum = project.votes.sum(:value) ... isn't really effective. Is there...

SQL aggregate functions using MIN and AVG...

How can I omit Zeros in my data? For example using the MIN function? I would like the Minimum value except a 0... How can I get the next largest? MIN(availables.price) Also is there a generally way to skip over 0s in the same way if I'm using the AVG function? The problem is the table I've inherited doesn't use NULL values but has...

SQL - counting WHERE AGGREGATE>1

Imagine I have a db table of Customers containing {id,username,firstname,lastname} If I want to find how many instances there are of different firstnames I can do: select firstname, count(*) from Customers group by 2 order by 1; firstname | count(*) ==================== bob | 1 jeff | 2 adam | 5 ...

Linq query for data aggregation

I have this class public class Line { public string ConnectionsIndex{get;set;} } my Linq problem is that I have to aggregate these Lines var l1 = new Line{ ConnectionsIndex="01,02"}; var l2 = new Line{ ConnectionsIndex="02,03"}; var l3 = new Line{ ConnectionsIndex="01,03"}; into this var l4 = new Line{ ConnectionsIndex="01,02,03...

LINQ Aggregate vs. nested foreach

I am trying to achieve: foreach (ScheduleItem s in ScheduleItems) { foreach (IScheduleModule m in s.ScheduleModules) { yield return m; } } using LINQ aggregate and I do not understand why return ScheduleItems.Aggregate(new Collection<IScheduleModule>(), (x, o) => x.Union(o.ScheduleModules) as Collection<IScheduleM...

How can you find the rows with equal columns?

If I have a table with important 2 columns, CREATE TABLE foo (id INT, a INT, b INT, KEY a, KEY b); How can I find all the rows that have both a and b being the same in both rows? For example, in this data set id | a | b ---------- 1 | 1 | 2 2 | 5 | 42 3 | 1 | 42 4 | 1 | 2 5 | 1 | 2 6 | 1 | 42 I want to get back all rows exce...

SQL group by day, with count

I've got a log table in SQL Server that looks like this: CREATE TABLE [dbo].[RefundProcessLog]( [LogId] [bigint] IDENTITY(1,1) NOT NULL, [LogDate] [datetime] NOT NULL, [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RefundId] [int] NULL, [RefundTypeId] [smallint] NULL, [LogMessage] [varchar](1000) COLLATE S...