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

SELECT records that have top n counts for one column

I am using postgresql 9.2. I have a dataset like this: ID A B 1 x x 2 x x 2 x x 2 x x 3 x x 4 x x 4 x x I want to display records with ID that has the top n count. Say, top 2 counts of ID--in this case, ID=2 and…
kuki
  • 303
  • 2
  • 6
  • 15
2
votes
1 answer

Count number of records returned by each group by

I have a general query such as: SELECT col1, col2, col3, col4 FROM Table1 AS t1 GROUP BY col1, col2, col3, col4 It will return a table something similar to: col1 col2 col3 col4 data1 data2 data3 data4 data1 data2 data3 data5 data10…
Andrew
  • 2,013
  • 1
  • 23
  • 38
2
votes
1 answer

Join across multiple tables with partial counts

Each company, has products and each product has entries in detail1, detail2, detail3 table. **Table Company** cid | cname -----+----------- 100 | Company 1 101 | Company 2 **Table Product** pid | cid | dname ------+-----+----------- 1000…
aquitted-mind
  • 263
  • 1
  • 13
2
votes
1 answer

Why do I get "ORA-00932: inconsistent datatypes: expected - got -" when using COLLECT() in a prepared statement?

I am using this query with the Perl DBI: SELECT c.change_id , COLLECT(t.tag) AS the_tags FROM changes c LEFT JOIN tags t ON c.change_id = t.change_id WHERE c.project = ? GROUP BY c.change_id The DBI uses OCI to prepare this statement,…
theory
  • 9,178
  • 10
  • 59
  • 129
2
votes
0 answers

SSDT emits spurious SQL71502: Function: [dbo].[***] contains an unresolved reference to an object

I have a number of SqlClr aggregate functions that were once in a separate project...but now with SSDT, I can have them all a single db project. Cool. All of them are in the global namespace. They compile and everything works...but I still get…
Clay
  • 4,999
  • 1
  • 28
  • 45
2
votes
1 answer

SQL Server Progressive/Compound Subtraction?

I have a table that looks like the below. I am trying to figure out how to update the "endval" column with a value that is from the "startval" column, reduced by 10% for every multiple of 5 in the "effect" column. declare @tbl table ( rowid int ,…
Snowy
  • 5,942
  • 19
  • 65
  • 119
2
votes
1 answer

Sum related properties in entity framework

I have a problem with sum of navigation properties using entity framework Here is my example classes public class Customer { public int Id { get; set; } public string Name { get; set; } public virtual ObservableCollection Calls {…
Ruben Alves
  • 41
  • 1
  • 3
2
votes
2 answers

Select columns other than the one specified in GROUP BY clause

Is there a way to select columns other the one specified in the group by clause? Let's say I have the following schema: Student(id, name, age), Course(id, name, credit), Enrollment(student_id, course_id, grade) I want to query for each course the…
0x56794E
  • 20,883
  • 13
  • 42
  • 58
2
votes
3 answers

How do I define a Standard Deviation function in Pentaho Schema Workbench

I'm building an OLAP Analysis with Pentaho's BI Suite (Community Edition). Many of my measures are standard deviations of the variables in my fact tables. Does someone has a tip on how to define a Standard Deviation aggregation function in Schema…
Lucas Soares
  • 117
  • 1
  • 2
  • 12
2
votes
1 answer

Is there any good way to build a comma-separated list in SQL Server?

In Firebird, there's an aggregate called List() that turns multiple results into a comma-separated string. This function does not appear to exist in SQL Server. Is there any equivalent to it that doesn't involve a big, long, ugly, slow workaround…
Mason Wheeler
  • 82,511
  • 50
  • 270
  • 477
2
votes
2 answers

Textbox item not linked to a dataset

In the tablix if've got 2 fields (Quantity and Price) that is populated from the database (dataset fields) witch works great. Next to those two columns I added another column Total which I use to get the total of the quantity * Price …
Kerieks
  • 1,042
  • 7
  • 25
  • 53
2
votes
1 answer

Update with a Join, Group By, and Having

The select statement executes with no errors or warning. The update statement throws an error: Incorrect syntax near the keyword 'group'. select [sSVsys].[textUniqueWordCount], count(*) as [actCount] from [docSVsys] as [sSVsys]with (nolock) join…
paparazzo
  • 44,497
  • 23
  • 105
  • 176
2
votes
1 answer

Query using aggregation and/or groups in relational algebra - count, max, min, etc

I have read much in textbooks and browsed a lot of pages on the internet but I can't understand how functions/operators like min, max, count, ... that aggregate over a relation/table or groups of tuples/rows in a relation/table are built with basic…
suti
  • 181
  • 4
  • 15
2
votes
3 answers

Compute percents from SUM() in the same SELECT sql query

In the table my_obj there are two integer fields: (value_a integer, value_b integer); I try to compute how many time value_a = value_b, and I want to express this ratio in percents. This is the code I have tried: select sum(case when o.value_a =…
Stephane Rolland
  • 38,876
  • 35
  • 121
  • 169
2
votes
1 answer

PostgreSQL cumulative count within last X months

Given the following table: CREATE TABLE cnts( user_id INT, month_d DATE, cnt INT ) I want to query cumulative counts for the last 6 months of each (user_id, month_d) pair. I can do it with the following JOIN: SELECT S1.month_d AS…
huy
  • 4,782
  • 6
  • 36
  • 42
1 2 3
…
99 100