aggregate

Average a time value in SQL Sever 2005

I've got a varchar field in SQL Sever 2005 that's storing a time value in the format "hh:mm"ss.mmmm". What I really want to do is take the average using the built in aggregate function of those time values. However, this: SELECT AVG(TimeField) FROM TableWithTimeValues doesn't work, since (of course) SQL won't average varchars. Howe...

Are two programs installed by the same Setup package still an aggregate under GPL?

Let's say I have two applications: one is the "server" application and licensed under GPLv3 and the other is the "client" application and is licensed under a non-GPL compatible license. The server runs as a Windows service, and the client is a normal windows application, and they communicate only via TCP/IP and XML (no linking at all). ...

Modify postgresql "list" aggregate to remove duplicates

One of my favorite postgres aggregates is "list", attributed to "Chris Rohlfs in the idocs" according to the scanty evidence I can find on the web. CREATE FUNCTION comma_cat (text, text) RETURNS text AS 'SELECT CASE WHEN $2 is null or $2 = '''' THEN $1 WHEN $1 is null or $1 = '''' THEN $2 ELSE $1 || '', '' || $2 END' L...

A work around Group By Clause Limitation

I'm working on a social network web application, and i got a situation where i need to resend reminder emails to users who haven't activated their emails. The problem is when i investigated the DB i found that many emails are duplicated (there was no validation on the email uniqueness apparently. So what i need to do know is to retrieve ...

SAS Web Report Studio - how to count distinct values in crosstabs

Hello, in SAS Web Report Studio 3.1 I need to count distinct occurences for selected dimensions and display the results in a crosstable. According to the SAS Support Web site this aggregate function is not supported in cross tablulations, but maybe there is a work-around? Thank you and best Regards Nadine ...

Sql Aggregate Results of a Stored Procedure

I currently have a stored procedure that returns a list of account numbers and associated details. The result set may contain multiple entries for the same account number. I also want to get some aggregate information such as how many distinct accounts are contained within a particular result set. Is there some way to retrieve such a vie...

How to retrieve the total row count of a query with TOP

I have a SQL Server 2008 query SELECT TOP 10 * FROM T WHERE ... ORDER BY ... I'd like to get also the total number of the rows. The obious way is to make a second query SELECT COUNT(*) FROM T WHERE ... ORDER BY ... Is there an efficient method? Thanks ...

T-SQL GROUP BY: Best way to include other grouped columns

I'm a MySQL user who is trying to port some things over to MS SQL Server. I'm joining a couple of tables, and aggregating some of the columns via GROUP BY. A simple example would be employees and projects: select empID, fname, lname, title, dept, count(projectID) from employees E left join projects P on E.empID = P.projLeader group by...

Select COUNT() from multiple databases in SQL

Hello, I am attempting to return the number of customers located in a specific state that have rented a specific movie, where the rents table contains two columns, one for customer ID and one for the movie ID. The function takes in a movie ID and the state and returns an integer with the amount of customers. Right now I have an implemen...

linq aggregate

class Category { public string Name { get; set; } public int Count { get; set;} } Name Count AA 2 BB 3 AA 4 I have an IEnumerable<Category> and would like to get a list of Categories with unique names and the sum of multiple entries Output Name Count AA 6 BB 3 Update class Category { public string Na...

Should a Model for a View be an Aggregate with its own Repository?

Should the Model that will be passed to the view be completely defined by a single call to a single repository. In other words, is the Model a single Aggregate, or should my Model be constructed from separate Aggregates, each with its own Repository, in the service layer? The way I have it now, is I simply call a single repository to fi...

Linq - How to aggregate the results of another query

Hi, I want to take the results of a where clause on a list and then take that result set and create just one new type that has all its fields constructed from aggregates of the orginal query. So given the basic example below, is there anyway to combine the 2 linq statements into one? If the orginal where has no rows then it should re...

how to get NHibernate aggregate function sum() to return decimal?

Hi, I can't get sum() to return decimal and it always returns int64 truncating the decimals. I have Googled for a whole day but still can't find a real work around. I have a DB table called ProductPurchase with QtyPurchased(int) and UnitPurchasePrice(money) columns, these are mapped to a C# POCO object using NHibernate, where Qt...

Coalesce and Pivot in TSQL

I am having trouble figuring out how to coalesce or pivot on a SQL recordset that looks like this: ID VALUE GROUP 3 John 18 4 Smith 18 5 Microsoft 18 3 Randy 21 4 Davis 21 5 IBM 21 etc and I want formatted like this NEWVALUE GROUP Smith, John (Microsft) 18 Davis, Randy (IBM) 21 thanks for any su...

SQL Find most recent date from 2 columns, AND identify which column it was in

I have a table called tblAssetsInUse with the following structure: intPK intAssetID datCheckedOut datCheckedIn 1 450 1/5/2009 10/5/2009 2 300 2/5/2009 <NULL> 3 200 2/5/2009 <NULL> 4 450 12/5/2009 5/7/2009 ...

Selecting date intervals, doing it fast, and always returning the latest entry with the result.

I have a database with a table, storing changes in account-balance across a couple of accounts, with three columns; float balance, #The account balance after the change Date date, #Date that balance change occurred int aid #Account that the balance change occurred on It contains a couple of entries for each day of the...

Optimizing MySQL Aggregation Query

Hi All, I've got a very large table (~100Million Records) in MySQL that contains information about files. One of the pieces of information is the modified date of each file. I need to write a query that will count the number of files that fit into specified date ranges. To do that I made a small table that specifies these ranges (all i...

What Belongs to the Aggregate Root

This is a practical Domain Driven Design question: Conceptually, I think I get Aggregate roots until I go to define one. I have an Employee entity, which has surfaced as an Aggregate root. In the Business, some employees can have work-related Violations logged against them: Employee-----*Violations Since not all Employees are subject...

Count distinct and Null value is eliminated by an aggregate

I'm using SQL Server 2005. With the query below (simplified from my real query): select a,count(distinct b),sum(a) from (select 1 a,1 b union all select 2,2 union all select 2,null union all select 3,3 union all select 3,null union all select 3,null) a group by a Is there any way to do a count distinct without getting "Warning: Null...

Advantage using an aggregate initialization list over a constructor?

Hello, I'm new to C++ and I have a question... I tried answering the question myself by making a test application... in debug, the class B initialization generates less assembly code, but in release mode, I can't really say... it optimizes the initializations away :( Let's say I have two classes: class A { public: int a, b, c, d;...