aggregate

NHibernate HQL - select count(*) with having - can't get it to work

Trying to run the following HQL with NHibernate: select count(distinct t) as TweetCount from Tweet t join t.Tweeter u left join t.Votes v left join t.Tags tag where t.App = :app having count(distinct v) > 0 But for some reason the having clause is being ignored and it's counting all tweets when only 2 tweets have a vote. I...

Map Reduce count number of documents in each minute MongoDB

I have a MongoDB collection which has a created_at stored in each document. These are stored as a MongoDB date object e.g. { "_id" : "4cacda7eed607e095201df00", "created_at" : "Wed Oct 06 2010 21:22:23 GMT+0100 (BST)", text: "something" } { "_id" : "4cacdf31ed607e0952031b70", "created_at" : "Wed Oct 06 2010 21:23:42 GMT+0100 (BST)",...

why is there no PRODUCT aggregate function in sql?

Im looking for something like SELECT PRODUCT(table.price) FROM table GROUP BY table.sale similar to how SUM works. Have I missed something on the documentations or is there really no product function? If so I wanted to know the reason why? Note: I looked for the function in postgres, mysql and mssql and found none so I assumed all sql...

using max() and sum()

I need to find the 'name of the branch that has made the most money in 2009'. My tables are below: Rental (cid, copyid, outdate, returndate, cost) Copy (copyid, mid, bid) Branch (bid, bname, baddress) I have written the following code, and it outputs the sum of all branches, but I need the sum of the branch that made the most money...

Working with Aggregates in DDD

Looking for some clarification on working with aggregate roots. If I have a model (a question paper) as follows; QUESTION PAPER ---> QUESTION ---> ANSWER and I have identified that the QUESTION PAPER is an aggregate root, if I want to select a answer for a question do I have to put a public method on the aggregate root or can I expose...

Sum up the aggregrate in SQL Server or ORACLE

I am running the query below and need to include a total at the bottom but not sure how to run it in SQL SERVER. (I can copy the data and do an auto sum in Excel but it's a bit repeatative) Could some one please help? SELECT LOCATION, SUM(CASE WHEN my_date >= '10/1/2009' AND my_date <'01/01/2010' THEN QTY ELSE NULL END) AS QTR1, SU...

C# HashSet union on IEnumerable within LINQ Func expression does not work (possible precompiler bug)

I am using using Microsoft .NET Framework 4.0. I have run into this using Aggregate on a Dictionary<T, List<T>> to extract the set of type T values used across all type List<T> lists in the dictionary. Here is the simplest case I could come up with that exhibits the same behaviour. First, as the documentation states, the following does...

How to do a within-group aggregate query in MySQL?

I am trying to get the latest modified "posts", from a group of "posts", that all have the same parent_id. I already learned today that grouping is not the answer, and that I should either use an inner subquery, or a "within-group aggregate", thanks to this site >> Aggregates >> Within-group aggregates. I chose to go with the latest, as...

How can I get the average value of a column with Core Data?

Hi Guys, If I have the following NSManagedObject, how can I get the average of number1 and number2. @interface Log : NSManagedObject { } @property (nonatomic, retain) NSNumber * number1; @property (nonatomic, retain) NSNumber * number2; Thanks :D ...

Retrieving aggregate values from multiple tables

I'm trying to get some aggregate values from different tables, but my problem is that they're being returned incorrectly, i.e. as multiplications of each other. I've got these tables: Institutes ---------- ID name Conversions ----------- ID institute_id training_id Trainings --------- ID institute_id And I want to do get the Instit...

plsql query return result into single row as concat

select col1 from tablename returns 2 rows, i want to concat thse two rows data into single column as comma separated. ...

sql pivot with no aggregate

Whilst trying to pivot a sql table I came across this post Here . By using this method I have created a query. However i have now realised that it of course aggregates the results with the MAX function. However I need the Colum to pivot but for all occurrences to be show. Code taken from above post. SELECT dy, MAX(CASE WHE...

DISTINCTCOUNT with FILTERing -1 IDs

Hi, Please bear with me as I am newbie in SSAS/MDX. I want DISTINCTCOUNT measure on ID column of my fact table but after filtering -1 i.e. unknown IDs. I like to slice/dice on it as well. Here are my tables DimStudent ---------------------------- ID Name -1 Unknown 1 Joe 2 Tim 3 P...

Create a panel from two data frames

I have two data frames -- one with stock closing prices arranged by date (rows) and ticker symbol (columns): > head(data.stocks) date A AAPL ABAT AB ABV 1 2010-10-04 32.59 278.64 3.65 26.42 125.89 2 2010-10-05 33.04 288.94 3.66 27.10 129.05 3 2010-10-06 32.67 289.19 3.59 26.99 129.90 4 2010-10-07 33.20 289.22 3.66 27...

How can i aggrategate/pivot this data?

I have a set of records that keeps track of system availability. Sample data: System_ID Event DateOfEvent 1 Down 2010-05-01 13:20:10 1 Up 2010-05-01 13:25:19 1 Down 2010-05-05 10:12:12 1 Up 2010-05-06 14:10:16 2 Down 2010-05-05 20:22:22 2 Up 2...

Max within a timeframe with date duplicates

I have a table like this DateTime start_time not null, DateTime end_time not null, Status_Id int not null, Entry_Id int not null I want to get the count of each status within a time period, where only the last started is valid for a given entry_id. What I am using now is this (with dynamic dates): with c (Status_Id, Entry_Id, Start_...

Evaluating the mean absolute deviation of a set of numbers in Oracle

I'm trying to implement a procedure to evaluate the median absolute deviation of a set of numbers (usually obtained via a GROUP BY clause). An example of a query where I'd like to use this is: select id, mad(values) from mytable group by id; I'm going by the aggregate function example but am a little confused since the function need...