Stack Overflow
Stack Overflow

Questions tagged [aggregate-functions]

Aggregate functions are a subset of SQL functions that compute a single value from multiple input rows, mostly used in `SELECT` queries with a `GROUP BY` clause. Practically all modern RDBMS feature aggregate functions. Typical examples include `COUNT()`, `SUM()`, `MIN()`, `MAX()`, and `AVG()`.

Aggregate functions are a subset of SQL functions that compute a single value from multiple input rows, mostly used in SELECT queries with a GROUP BY clause. Practically all modern RDBMS feature aggregate functions.

Typical examples include COUNT(), SUM(), MIN(), MAX(), and AVG().

5778 questions
2
votes
2 answers

Left outer join acting like inner join

Summary My goal is to find every user who has ever been assigned to a task, and then generate some statistics over a particular date range, and associate the stats with the original set of users. When no statistics exist for a particular user, I…
Phrogz
  • 296,393
  • 112
  • 651
  • 745
2
votes
3 answers

Aggregate Function - First nvarchar in custom order

I have a table with the following data: TableColumnsId AssumedDataType Ranking 43264 System_String 1 43265 System_Int32 2 43265 System_Double 10 43266 System_Double 10 43266 …
Rafi
  • 2,433
  • 1
  • 25
  • 33
2
votes
1 answer

Symfony approach to displaying aggregate entity data

I have 3 entities Expertise, Person and Tag, related through a join entity, AssociatedTag which has a composite primary key (expertise_id, tag_id, person_id). This works correctly. Because more than one Person can add the same Tag to a particular…
dnagirl
  • 20,196
  • 13
  • 80
  • 123
2
votes
2 answers

MySQL: aggregate aggregated values

Consider this simple query: SELECT year(t.date) AS y, month(t.date) AS m, t.person_id AS id, count(*) AS freq FROM table t WHERE t.date>='2013-01-01' AND t.date<='2013-06-30' GROUP BY y, m, id This yields something…
thofou76
  • 77
  • 8
2
votes
1 answer

Joining Summed data that has nulls - SQL Server

How do I retain the Acct_Name field where appropriate when summing the data below by the Amount column and grouping by the Line_Num field? The "Null" values in Line_Num column cause a problem in the grouping terms when the account name is added.…
Shrout1
  • 2,497
  • 4
  • 42
  • 65
2
votes
1 answer

linq aggregated nested count

i have the following classes: class Outer { public ICollection Inners } class Inner { public ICollection Inners } I would like to order descending a list of outers by the total count of their Inners and nested Inners. for…
user2355293
  • 55
  • 1
  • 7
2
votes
5 answers

Find pair of students who take exactly the same classes

I have to find a pair of students who take exactly the same classes from table that has studentID and courseID. studentID | courseID 1 1 1 2 1 3 2 1 3 1 3 2 3 …
Boseo
  • 87
  • 1
  • 5
2
votes
3 answers

Trending sum over time

I have a table (in Postgres 9.1) that looks something like this: CREATE TABLE actions ( user_id: INTEGER, date: DATE, action: VARCHAR(255), count: INTEGER ) For example: user_id | date | action |…
John Ledbetter
  • 13,557
  • 1
  • 61
  • 80
2
votes
1 answer

How to get first available value in a column (not MIN) using aggregate function on a NVARCHAR type column?

I have a temp table (#TempTable) which looks like this: AID StartTime EndTime StartSID EndSID Name 79F05D45 2013-07-02 2013-07-03 1226349 1227338 79F05D45 2013-07-03 2013-07-03 1227381 1227901 79F05D45 …
Learner
  • 3,904
  • 6
  • 29
  • 44
2
votes
3 answers

How can I reference an aggregate column in another column in the same query?

I have a query that looks more or less like this: INSERT INTO #results SELECT Name, (SELECT SUM(CAST(Amount AS BIGINT)) FROM Items WHERE RemittingMember = a.Number and RecordId = 50), …
ldam
  • 4,412
  • 6
  • 45
  • 76
2
votes
2 answers

postgresql aggregate of aggregate (sum of sum)

I've got workers who have many sales and who belong to departments. I'd like to see how many sales a department is making per day. For simplicity, let's say a worker belongs to only one department. Example: departments: | id | name | | 1…
Tyler DeWitt
  • 23,366
  • 38
  • 119
  • 196
2
votes
1 answer

T-SQL sum() of rand(checksum(newid()) is not random if appeared multiple times in the select list?

Expression rand(checksum(newid())) is often used to generate random numbers. While generating some test data, I executed following statement: select rand(checksum(newid())) R1, rand(checksum(newid())) R2 from ftSequence(3) where ftSequence(N) is a…
i-one
  • 5,050
  • 1
  • 28
  • 40
2
votes
1 answer

Using Aggregate Functions Inside a Trigger In MySQL

I have a 'People' table with several attributes including 'age'. Each time I insert a new tuple into this table, I would like to find out the average age of all the people listed in the table. If the average is above 50, I want to modify the age in…
James Bound
  • 31
  • 5
2
votes
1 answer

SQL Aggregation With Condtion on aggregated value

I want to take aggregate of NetQuatity from table BillDetails where this aggregated value should non-zero. I wrote a query as given below. But feels like it is not optimized. Is there anyone can refract this one. Any attempt will be…
N K
  • 3,217
  • 5
  • 23
  • 38
2
votes
1 answer

Cannot reference alias of aggregate function in where clause of subquery

I'm having problems with this select statement: SELECT field1, MIN(field2) AS MinOfField2, (SELECT id FROM table2 WHERE something = MinOfField2) AS table2_id FROM table1 GROUP BY field1 When I try to execute this query, access pops up a…
Reto Höhener
  • 5,419
  • 4
  • 39
  • 79
1 2 3
…
99 100