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

How to include missing data for multiple groupings within the time span?

I have below referenced query which groups studies counts by teacher, study year-month, and room for the past 12 months (including current month). The result I get is correct, however, I would like to include rows with zero counts for when the data…
zam6ak
  • 7,229
  • 11
  • 46
  • 84
2
votes
2 answers

How can I combine sql aggregate functions with distinct more cleanly?

I've been running into the following case repeatedly lately, either I need to apply MAX() or SUM() to one column on a table, but I need DISTINCT sets of values for other columns. For example consider the following table and associated columns…
Thomas Langston
  • 3,743
  • 1
  • 25
  • 41
2
votes
2 answers

SQL MAX and MIN in one column

I have a problem to get the max and the min value, I want the result to be XXL and XXS SELECT MAX(tblSizeXL.SizeXLName) AS maxSize, MIN(tblSizeXL.SizeXLName) AS minSize FROM Product JOIN tblSizeXL ON Product.SizeXLID =…
Nicklas
  • 23
  • 1
  • 3
2
votes
2 answers

SQL - using sum() to retrieve the first row where it gets to a value

I have this table, let's call it Lines: id | id_sale | id_product | quantity 1 1 1 4 2 2 1 4 3 3 1 4 4 4 1 4 It represents the lines of a sale. And…
Duarte Mendes
  • 161
  • 3
  • 12
2
votes
2 answers

summarize counts from multiple columns in a single table

I want to display count of data of two category on single display my table like below : table : funding area client Donatur ----------------------------------------- A Ox Mr.X A Pr Mr.Y A Qs Mr.Z A …
user2967389
  • 53
  • 6
2
votes
1 answer

Getting multiple counts across joined tables

I have 3 related tables: business id name inspection id business_id date violation id inspection_id title I'd like to get out: [business.id, Count(Inspections), Count(Violations)] Where Count(Inspections) is the total number of…
rstocker99
  • 23
  • 2
2
votes
2 answers

How to get average based on list of Ids in entity framework

I have a table let says Products which contains two column(ProductId, Price). I am getting list of product Ids from client(IOS, Android). Instead of iterating over the list and getting the price one by one and then calculate average, Is there any…
Jyotish Singh
  • 2,065
  • 3
  • 20
  • 28
2
votes
4 answers

Counting the number of instances of some data in a mySQL column

I have 1 table called urltracker that holds information about the number of times a specific url has been clicked. The table is structured into 4 columns id, urlclicked, referrer and timestamp. The urltracker creates another entry in the table every…
colin
  • 121
  • 1
  • 6
2
votes
2 answers

Custom aggregations for Spark dataframes

I was wondering if there is some way to specify a custom aggregation function for Spark dataframes. If I have a table with 2 columns id and value I would like to groupBy id and aggregate the values into a list for each value like so: from: john |…
anthonybell
  • 5,790
  • 7
  • 42
  • 60
2
votes
2 answers

How to use sum(count()) in mysql

I wish to get the total number of users registered with my site in the date range selected like as follows: 2 users logged in on 1st June 1 user on 2nd June, 2 users on 3rd June Then I need the count for 1st June as 2 2nd June as 3 3rd June as…
Sujitha M
  • 391
  • 1
  • 2
  • 10
2
votes
1 answer

How to group by multiple columns in dataframe using R and do aggregate function

I have a dataframe with columns as defined below. I have provided one set of example, similar to this I have many countries with loan amount and gender variables country loan_amount gender 1 Austia 175 …
SRS
  • 439
  • 9
  • 19
2
votes
2 answers

MySQL Query - Using Aggregate and Group By to generate separate results

I have a table where one or more entries with the same 'id' value can be inserted into our log / fact table (contains over 100+ million records) At a set frequency a new record is inserted into this table with a new value for the columns 'created'…
Scott
  • 23
  • 4
2
votes
3 answers

How can I check for a certain value in all aggregated rows?

Suppose I have three tables: user, group and xref, a table that gives them many-to-many RI. I might want to see how groups each user belongs to: select user.user_id, user.user_name, count(*) as group_count from user inner…
Max Cantor
  • 8,229
  • 7
  • 45
  • 59
2
votes
1 answer

Use of array_agg and array string on inner query

I have the following piece of code: DROP SCHEMA IF EXISTS s CASCADE; CREATE SCHEMA s; CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); INSERT INTO s.t1 (c2) VALUES…
gudge
  • 1,053
  • 4
  • 18
  • 33
2
votes
0 answers

Solr group query based on the sum aggregation of function query

I have tried to implement the Page and Post relation in single Solr Schema. In my use case each page has multiple posts. Page and Post fields are as follows: Post:{post_content, owner_page_id, document_type} Page:{page_id, document_type} Suppose I…
Ali
  • 1,759
  • 2
  • 32
  • 69
1 2 3
…
99 100