aggregate-functions

SQL Server 2005 Computed Column Result From Aggregate Of Another Table Field's Value

Sorry for the long question title. I guess I'm on to a loser on this one but on the off chance. Is it possible to make the calculation of a calculated field in a table the result of an aggregate function applied to a field in another table. i.e. You have a table called 'mug', this has a child called 'color' (which makes my UK head hu...

Fetching Minimum/Maximum for each group in ActiveRecord

This is an age-old question where given a table with attributes 'type', 'variety' and 'price', that you fetch the record with the minimum price for each type there is. In SQL, we can do this by: select f.type, f.variety, f.price from ( select type, min(price) as minprice from table group by type ) as x inner join table as f on f....

MySQL: Select N rows, but with only unique values in one column

Given this data set: ID Name City Birthyear 1 Egon Spengler New York 1957 2 Mac Taylor New York 1955 3 Sarah Connor Los Angeles 1959 4 Jean-Luc Picard La Barre 2305 5 Ellen Ripley Nostromo 2092 6 James T. Kirk Riverside 2233 7 Henry Jones Chica...

How to minus instead of add in a Sum() like sql function

I have a group by clause in a sql statement and need to use an aggregate function to minus all the values in each group instead of adding like the Sum() function. i.e. SELECT Sum(A) FROM ( SELECT 2 AS A UNION SELECT 1) AS t1 ..so will evaluate 2+1 and return 3. I need some way of doing 2-1 to return 1. Hope this makes s...

sql - Using aggregate functions (min/max) as part of select statement

Hello everyone, I am trying to return the minimum and maximum prices for a villa booking system. I have a look up table that stores the price for each week for each villa. I am using the min and max functions to do this within the select but I'm having lots of problems. Can anyone explain where i'm going wrong? Heres the sp ALTER PRO...

MySQL: Use Aliased Fields in Aggregate Functions

I have a SQL statement similar to this: SELECT COUNT(*) AS foo, SUM(foo) AS foo_sum FROM bar But MySQL doesn't allow this because foo is an alias. Does anyone have an idea of how this could be accomplished in SQL? ...

Aggregate functions in WHERE clause in SQLite

Simply put, I have a table with, among other things, a column for timestamps. I want to get the row with the most recent (i.e. greatest value) timestamp. Currently I'm doing this: SELECT * FROM table ORDER BY timestamp DESC LIMIT 1 But I'd much rather do something like this: SELECT * FROM table WHERE timestamp=max(timestamp) Howeve...

MySQL add total column

I need to query this DB to get each row, but also the SUM of one of the column values of the results. I could use php to get the total value, but then I'd need to run two loops, one to get the total (which goes at the top above the results). So I'd prefer the query to catch it and just make a "total" row, but the only way I've gotten it ...

LINQ to SQL: Complicated query with aggregate data for a report from multiple tables for an ordering system

I want to convert the following query into LINQ syntax. I am having a great deal of trouble managing to get it to work. I actually tried starting from LINQ, but found that I might have better luck if I wrote it the other way around. SELECT pmt.guid, pmt.sku, pmt.name, opt.color, opt.size, SUM(opt.qty) AS qtySol...

Group by Max

SELECT tblIssue.SYMB, tblIssue.[PRCE], tblIssue.[Shareholder] FROM tblIssue I am trying to pull the symb and price for the maximum number of shareholder per symb. For example, I would have only 1 line for ASN where the price would be $60.62. SYMB Price Shareholder ASN $0.00 0 ASN $0.00 51 ASN $25.18 0 ASN $25.26 0 ASN $36.00 0 ASN...

SQL composite key query

to reference this again http://stackoverflow.com/questions/501347/sql-products-productsales how could i do something like that when the primary key is made up of two columns and not one? so products has two columns as PK, and productsales has two columns as FK. here is the solution with a 1-column key: SELECT p.[name] FROM products p...

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...

Extra Fields with SQL MIN() & GROUP BY

When using the SQL MIN() function, along with GROUP BY, will any additional columns (not the MIN column, or one of the GROUP BY columns) match the data in the matching MIN row? For example, given a table with department names, employee names, and salary: SELECT MIN(e.salary), e.* FROM employee e GROUP BY department Obviously I'll get...

Does GQL support commonly available SQL Style aggregation?

What I'm looking for a simple Aggregate Functions that are widely available in versions of SQL. Simple things like Select Count(*) from table1 to the more complex. If these are available, is there some documentation you could point me to? Thanks - Giggy ...

Aggregate SQL Function to grab only the first from each group

I have 2 tables - an Account table and a Users table. Each account can have multiple users. I have a scenario where I want to execute a single query/join against these two tables, but I want all the Account data (Account.*) and only the first set of user data (specifically their name). Instead of doing a "min" or "max" on my aggregate...

SQL AVG(COUNT(*))?

Hi, I'm trying to find out the average number of times a value appears in a column, group it based on another column and then perform a calculation on it. I have 3 tables a little like this DVD ID | NAME 1 | 1 2 | 1 3 | 2 4 | 3 COPY ID | DVDID 1 | 1 2 | 1 3 | 2 4 | 3 5 | 1 LOAN ID | DVDID |...

Group related records, but pick certain fields from only the first record

I'm preforming an aggregate function on multiple records, which are grouped by a common ID. The problem is, I also want to export some other fields, which might be different within the grouped records, but I want to get those certain fields from one of the records (the first one, according to the query's ORDER BY). Starting point exampl...

SSRS: How to make a sum that only includes the last item in a group

I have an rdlc report file, and I am trying to make a sum which can only include the last item in each group. I have a table kind of like this: Place = ? (Group header 1) User = ? (Group header 2) Date =Last(Fields!Number.Value) ...

Aggregate functions in ADO.NET with GROUP BY functionality

This is a more direct question stemming from an earlier more general question i had earlier now that I've spend more time looking into ADO.NET I want to take an ADO.NET DataTable and perform the equivalent of a SQL SELECT query with aggregate functions (such as SUM) on some columns, and GROUP BY set for the remaining columns. I then wan...

Aggregate query with Castle ActiveRecord

I'm trying to perform a simple aggregate query that returns the aggregate's result plus an extra column. This post -> http://stackoverflow.com/questions/308203/custom-query-with-castle-activerecord had a good example about how to achieve this, but I can't seem to get it to work. It seems that ActiveRecordMediator.ExecuteQuery returns an ...