I have a table with three columns: patient_id obs_date weight_val patient_id stores patient identification #, weight_val stores a weight value, and obs_date stores the date when the weight reading was taken. So, a patient can have many different weight readings at different dates. How do you write a query for: select all patient...
(I'm using postgres) Are there any aggregate functions that work on strings? I want to write a query along the lines of select table1.name, join(' - ', unique(table2.horse)) as all_horses from table1 inner join table2 on table1.id = table2.fk group by table1.name Given these 2 tables: | table1 | | table2 ...
I have a few (hopefully) simple questions about aggregate roots in domain driven design: Is it okay to have an aggregate root as a property of another aggregate root? Is it okay to have a given entity inside two or more aggregate roots? My final question is a bit more involved. I have a website that has a few entities that really bel...
I have a query that sums and aggregates alot of data something like this: var anonType = from x in collection let Cars = collection.Where(c=>c.Code == "Cars") let Trucks = collection.Where(c=>c.Code == "Trucks") select new { Total = collection.Sum(v=>v.Amount), ...
I tried to uses aliases in postgres for this query, but postgres stops and complains that ERROR: column "subtotal" does not exist SELECT SUM(price) AS subtotal, subtotal * 3.0 AS fees, (subtotal + fees) AS total FROM cart You cannot use aliases as part of your next column? ...
I have two models, authors and articles: class Author(models.Model): name = models.CharField('name', max_length=100) class Article(models.Model) title = models.CharField('title', max_length=100) pubdate = models.DateTimeField('publication date') authors = models.ManyToManyField(Author) Now I want to select all authors...
I have the following Oracle query: SELECT id, DECODE(state, 'Open', state_in, NULL) AS open_in, DECODE(state, 'Not Open', state_in, NULL) AS open_out, FROM ( SELECT id, CASE WHEN state = 'Open' THEN 'Open' ELSE 'Not Open' END AS state, T...
I'm having trouble getting my head around converting a traditional SQL aggregate query into a LINQ one. The basic data dump works like so: Dim result = (From i As Models.InvoiceDetail In Data.InvoiceDetails.GetAll Join ih As Models.InvoiceHeader In Data.InvoiceHeaders.GetAll On i.InvoiceHeaderID Equals ih.ID Join p As Mo...
Having just started working on a new project using Entity Framework 4, I spoke to some of the other team that use NHibernate for advice. They implement aggregate routes on their entities, so instead of adding an order through the orders entity, they would add it through customer.order by having an addOrder method on customer. This is th...
HI I have a table which holds files and their types such as CREATE TABLE files ( id SERIAL PRIMARY KEY, name VARCHAR(255), filetype VARCHAR(255), ... ); and another table for holding file properties such as CREATE TABLE properties ( id SERIAL PRIMARY KEY, file_id INTEGER CONS...
My application performs a number of queries with the general form: SELECT <aggregate-functions> FROM <table-name> WHERE <where-clause> GROUP BY <group-by column list> I would like to know how many records contributed to the aggregate result set (in other words, how many records matched the conditions in the WHERE clause), something th...
It's probably something silly I missed, but I try to concatenate a list of integers instead of summing them with: integerArray.Aggregate((accumulator, piece) => accumulator+"," + piece) The compiler complained about argument error. Is there a slick way to do this without having to go through a loop? ...
Hi, i've a problem for a table update. follow table structure: Table1 tableid ... ... productID_1 productID_2 productID_3 Table2 productID Total I've to totalize each product in table2. For example: SELECT COUNT(*) as tot, ProductID_1 FROM Table1 GROUP Table1 then the UPDATE table2 SET total =..??? (how can...
say I have a postgresql table with the following values: id | value ---------- 1 | 4 2 | 8 3 | 100 4 | 5 5 | 7 If I use postgresql to calculate the average, it gives me an average of 24.8 because the high value of 100 has great impact on the calculation. While in fact I would like to find an average somewhere around 6 and elimina...
I have two tables: a Files table, which includes the file type, and a File Properties table, which references the file table via a foreign key. Sample Files table: | id | name | type | --------------------- | 1 | file1 | zip | | 2 | file2 | zip | | 3 | file3 | zip | | 4 | file4 | jpg | And the Properties table: | file_id | ...
I am getting an exception when I try to get @sum on a column in iPhone Core-Data application. My two models are following - Task model: @interface Task : NSManagedObject { } @property (nonatomic, retain) NSString * taskName; @property (nonatomic, retain) NSSet* completion; @end @interface Task (CoreDataGeneratedAccessors) - (void...
I currently have a table which stores a load of statistics such as views, downloads, purchases etc. for a multiple number of items. To get a single operation count on each item I can use the following query: SELECT *, COUNT(*) FROM stats WHERE operation = 'view' GROUP BY item_id This gives me all the items and a count of their views. ...
I have data with variables minimum and maximum temperatures, month and years(1951-2001). I want to get a dataframe of average temperatures for each month in each year. I want the dataframe to look like this: Year jan feb mar apr may june..... 1951 xx xx 1952 xx 1953 . . ...
In DDD, Repository takes care of saving and retrieving domain objects and also serves as collection of Aggregate Roots. My question is how do you retrieve the information for those child entities (let's say from DB) of an Aggregate where basic rule is Repository should just hold collection of Aggregate Roots (parent object) and not child...
I am trying to write an aggregate udf for using Sql Server 2008 and C# 3.5 that implodes an aggregation of data. The kind of syntax I am looking for is: SELECT [dbo].[Implode]([Id], ',') FROM [dbo].[Table] GROUP BY [ForeignID] where the second parameter is the delimiter for the aggregate function. And example return value would be s...