aggregate

Cannot perform an aggregate function on a subquery

Can someone help me with this query? SELECT p.OwnerName, SUM(ru.MonthlyRent) AS PotentinalRent, SUM( (SELECT COUNT(t.ID) * ru.MonthlyRent FROM tblTenant t WHERE t.UnitID = ru.ID) ) AS ExpectedRent FROM tblRentalUnit ru LEFT JOIN tblProperty p ON p.ID = ru.PropertyID GROUP BY p.OwnerName I'm having problems with the se...

Availiable reducers in Elastic MapReduce

I hope I'm asking this in the right way. I'm learning my way around Elastic MapReduce and I've seen numerous references to the "Aggregate" reducer that can be used with "Streaming" job flows. In Amazon's "Introduction to Amazon Elastic MapReduce" PDF it states "Amazon Elastic MapReduce has a default reducer called aggregrate" What I wo...

sql statement to retrieve a list of results (from other table) for a given record?

I have the following schema: TABLE bands -> band_id -> property1 -> property2 -> ... TABLE tracks -> band_id -> track_id The tracks table typically maps a single band_id onto multiple track_ids (there are multiple tracks for each band). My goal is to build, for the bands that match certain conditions (in terms of property1, property...

SQL query minimum maximum

I need to get maximum and minimum values but also I need to get row id of these maximum or minimum on the same row. SELECT MIN([Value]), MAX([Value]), id FROM [AnalystEstimates].[dbo].[AnalystEstimateValues] GROUP BY indicatorid ...

Mock testing with Repository pattern example.

I'm refactoring existing class to support Products import from CSV file.   Requirements: 1) during import products are identified by special product number. 2) product has category attribute. If such category exist - use its id, if not - create it first. 3) if product with some number already exists in DB - do an update of other fie...

The max value of several columns (date and cost relationship)

How do you return the max value of several columns (date and cost relationship)? TableName [ID, Date1, Cost1, Date2, Cost2, Date3, Cost3] I need to return something like this: [ID, Most Recent Date, Cost] I need to return something like this: ID Most Recent Date the Cost related to the Date (if the most recent date is Date1 so th...

MYSQL COUNT GROUP BY question

I'd like to only select the rows where the count is greater than 1 (in other words the duplicates) right now from a few thousand records i am mostly seeing ones with a few 2s and 3s here and there SELECT count( * ) AS `Number` , GI . * FROM `GeneralInformation` AS GI GROUP BY `FirstName` , `Surname` how can I do this? ...

Using MYSQL GROUP_CONCAT in the WHERE clause

Is it possible to put GROUP_CONCAT in a MYSQL WHERE clause? I have two tables (one for members and one for payment info). For example Members Table num, memNumber, fullName, coporateName, surname 001, mem0010, Joe Bloggs, NULL, Bloggs 002, mem0015, NULL, BBC 003, mem0017, John Peters, NULL 004, mem0101, Emma Jane, NULL Payment Tabl...

DDD element: Aggregates in c#

When analyzing life cycle of domain objects, aggregate is basic element for objects grouping. I am having trouble implementing aggregetes in C#. One short example, with couple of classes, would be very helpful. Or any link on this theme. ...

Get the id of the next closest location

Hi. I have an application in which the user can select a location and view it's distance from several Points Of Interests (POIs). When I retrieve these distances, I would also like to retrieve the ID's of the locations that are the next closest and the next furthest away from each POI. eg. If we have 10 locations, each of them a mile f...

MySQL: Last 10 entries per user?

I have a table storing transactional data for users. To find a users rating you take the average score of the last 10 entries for that user. Is there a way to get that with SQL? I need to be able to work it out for a single user given their ID. And to get a list of all users ordered by their score. Currently I'm working it out outside ...

Pulling Master-Detail data from Oracle w/o separate lines for detail

Okay, I tried searching around, but couldn't come up with a decent idea on what to query on, so I figured I'd post a question. How can I pull back master/detail information without having the master data duplicated along with every line of detail data? Given the following scenario: Master - Cars ============= decimal car_id (pk) stri...

How to use awk to aggregate rows of data

Hi, I have a question, I have a set of data in rows which some rows are belong to a group. E.g Apple 0.4 0.5 0.6 Orange 0.2 0.3 0.2 Apple 0.4 0.3 0.4 Orange 0.4 0.5 0.8 The question is how can I automatically aggregate the columns accordingly using awk. In the past, I would easily deal with the following awk manually for each file....

Sharepoint - Showing calendar events in multiple calendars in diff't sub-sites

I am trying to show events from a calendar in the main site to a calendar in a sub-site of the main site (or vice-versa)...I can't alter any server files or use custom-built web parts...is there anyway to do this w/ OOTB moss 2007? ...

Querying for a unique value based on the aggregate of another value while grouping on a third value entirely

So I know this problem isn't a new one, but I'm trying to wrap my head around it and understand the best way to deal with scenarios like this. Say I have a hypothetical table 'X' that looks like this: GroupID ID (identity) SomeDateTime -------------------------------------------- 1 1000 1/1/01 1 1001 2/2/02 ...

Php in MYSQL aggrate function not working

$result = mysql_query("SELECT avg(r.rate) FROM rate r where ImgName='1'"); this php is not working. Originally my code is <?php $con = mysql_connect("localhost","root","sql"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("photogallery", $con); $result = mysql_query("SELECT avg(r.rate) FROM rate r ...

How must the mysql query be like to achieve the shown result?

Say, I have two tables like these: Table group Table user +----+-----------+ +----+----------+------+----------+ | id | groupname | | id | username | rank | group_id | +----+-----------+ +----+----------+------+----------+ | 1 | Friends | | 1 | Frank | 1 | 1 | | 2 | Family | | 2 | Mike | 3 | ...

Aggregate roots. How far does the rabbit hole go.

I'm trying to use the Repository pattern for my current project and i'm currently in the process of trying to model the domain and find the aggregate roots. I've read of the 'Cascading Delete' rule which states that if it doesn't make sense to delete a member when the root is deleted then it shouldn't be part of the root. I'll use a P...

Ok to provide constructor + trivial operators for behaviorless aggregates?

This is a follow-up question to 2043381. Consider the following: struct DataBundle { std::string name; int age; DataBundle() : age(0) {} DataBundle(const std::string& name, int age) : name(name), age(age) {} void swap(DataBundle& rhs) {name.swap(rhs.name); std::swap(age, rhs.age);} DataBundle& operator=...

Why can't indexed views have a MAX() aggregate?

I have been trying out a few index views and am impressed but I nearly always need a max or a min as well and can not understand why it doesn't work with these, can anyone explain why? I KNOW they are not allowed, I just can't understand why!!! Count etc. is allowed why not MIN/MAX, I'm looking for explanation... ...