Say I have classes like: public class ServiceCall { public int ServiceCallID {get; set;} public DateTime ReportedTimestamp {get; set;} public bool IsPaid {get; set;} public decimal LabourNet { get; set;} public decimal LabourVat {get; set;} } public class UsedPart { p...
I would like to ask for opinion on making aggregate data by concatenating strings. If I have a column aggregate but I want to concatenate then in an aggregate column, which is faster in terms of performance? Doing one SQL then just aggregating then in the CODE. Or selecting the main data then querying one at a time. For Example: TABLE_...
If my understanding of Aggregate Roots is correct, the root should be responsible also for deleting one of its "children". That would seemingly translate into something like this: order.removeOrderLine(23); Which would effectively remove it from the collection. However, how is this persisted? Is my ORM's UnitOfWork supposed to detect ...
Hi there! I have the following models, Art and ArtScore: class Art(models.Model): title = models.CharField() class ArtScore(models.Model): art = models.ForeignKey(Art) date = models.DateField(auto_now_add = True) amount = models.IntegerField() Certain user actions results in an ArtScore entry, for instance whenever y...
This is another stab into a problem I posted here. Please don't close as duplicate, because it goes in another direction. I'd like to automatically update a database column with an aggregate of another column. There are three tables involved: T_RIDER RIDER_ID TMP_PONYLIST ... T_RIDER_PONY RIDER_ID PONY_ID T_PONY PONY_ID ...
I have multiple abstract models similar to this Class Article(models.Model): title = models.CharField() body = models.TextField() created_date = models.DateTimeField() author_name = models.CharField() class Video(models.Model): title = models.CharField() body = models.TextField() created_date = models.DateTi...
Hi All, I have two choices when writing a SQL statement with COUNT function. SELECT COUNT(*) FROM SELECT COUNT(some_column_name) FROM In terms of performance what is the beast SQL statement? can I obtain some performance gain by using option: 1 ? Thanks, Upul ...
Hi, I have a query with several aggregate functions and then a few grouped columns. I want to take one of the grouped columns out of the group and perform some sort of aggregate "concatenating" of all the VARCHAR values it has. (Ideally in a new carriage separated list). Here is my query and I note where I'd like to do this: SELECT r...
I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following: id time_in time_out 1 2009-11-01 1 2009-10-30 2 2008-12-15 2 2009-02-03 I then do an outer query like so: SELECT id, MIN(time_in) AS time_in, MIN(time_out) A...
DUPLICATE: http://stackoverflow.com/questions/981375/using-a-django-custom-model-method-property-in-orderby I have two models; one that stores posts and another that stores votes made on those posts, related using a ForeignKey field. Each vote is stored as a separate record since I need to track the user and datetime that the vote was...
Is there a way to write SQL Server Stored Procedure which to be strongly typed ( i.e. returning known result set of columns ) and having its group statement to be dynamic. Something like: SELECT SUM( Column0 ) FROM Table1 GROUP BY @MyVar I tried the following also: SELECT SUM( Column0 ) FROM Table1 GROUP BY CASE @MyVar WHEN 'Column...
Hi, I would like to make a nice function to aggregate data among an array (it's a numpy record array, but it does not change anything) you have an array of data that you want to aggregate among one axis: for example an array of dtype=[(name, (np.str_,8), (job, (np.str_,8), (income, np.uint32)] and you want to have the mean income per j...
I have a query that takes avg data (prices) from 7 days of the week for a long interval. IE avg prices for monday, tues, etc. It works fine, but I'm unsure how I can in the same query sum the avgs that this query finds? Summing Day1..Day5 As it stands this query sums the entire from of all the prices... IE huge number.. not from the ...
Basically I want is to aggregate some values in a table according to a timespan. What I do is, I take snapshots of a system every 15 minutes and I want to be able to draw some graph over a long period. Since the graphs get really confusing if too many points are shown (besides getting really slow to render) I want to reduce the number o...
I am writing a custom report from an Avamar (Postgresql) database which contains backup job history. My task is to display jobs that failed last night (based on status_code), and include that client's success ratio (jobs succeeded/total jobs run) over the past 30 days on the same line. So the overall select just picks up clients that f...
I have a function that looks like this: def post_count(self): return self.thread_set.aggregate(num_posts=Count('post'))['num_posts'] I only want to count posts that have their status marked as 'active'. Is there an easy way to add a filter before the Count function? Model Definitions: class Category(models.Model): name =...
I have a table structure similar to the following example: DateTime V1 V2 V3 V4 10/10/10 12:10:00 71 24 33 40 10/10/10 12:00:00 75 22 44 12 10/10/10 12:30:00 44 21 44 33 10/10/10 12:20:00 80 11 88 12 With DateTime field being the unqiue and key field, I want...
I have a very simple grouping and aggregation problem in LINQ to SQL that I just can't figure out, and it is driving me mad. I've simplified things into this example: class Customer { public Guid Id; public String Name; } class Order { public Guid Customer_Id; public double Amount; } How do I get a list of customers order...
I have a table that has the following data: id status date part_no part_name 1 high 1/2/09 55 screw; 1 medium 1/2/09 55 screw; 2 high 2/2/09 32 plug; 3 low 4/8/09 59 bolt; 4 medium 5/6/09 48 tie; 4 low 5/6/09 48 tie; I want to write a query that will give me one r...
Given a datatable, I wish to output an IEnumerable type (dictionary(of T) would be perfect, otherwise a datatable would be acceptable) that provides an aggregation of the data within the datatable. In SQL I would write the query as such: select groupByColumn, sum(someNumber) from myTable group by groupByColumn In VB.NET the closest ...