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
5 answers

How do you group the SUM of rows by year in SQL

SELECT YEAR(OrderDate) 'Year', SUM(TotalDue) FROM Sales GROUP BY OrderDate Order BY OrderDate How do I add each year together as ONE row? I wrote the query above, but the result still has the TotalDue by Year as…
Eda
  • 218
  • 1
  • 3
  • 17
2
votes
6 answers

ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

Example data: test,test2 ---------- a,qwer b,wert c,erty d,rtuy d,tyui e,yuio e,uiop I am trying to write a query which returns unique values in a table: SELECT test FROM aatest GROUP BY test HAVING (COUNT(test) = 1) This works and returns a,b…
eggbert
  • 3,105
  • 5
  • 30
  • 39
2
votes
4 answers

Get rows with maximum count

I need to perform a query to get the candies that are most liked by kids and here's what I've got: SELECT COUNT(*) as counts,candies.name FROM candies INNER JOIN kid_candy ON kid_candy.candy_id = candies.id INNER JOIN kids ON kids.id =…
ronzohan
  • 105
  • 1
  • 8
2
votes
1 answer

postgresql do not aggregate by my timestamp

I have sql like following: with CTE as ( select user_id,level,plugged,plugged_ac,plugged_usb,created_at from probe_batteries where probe_batteries.user_id='91' and probe_batteries.created_at > timestamp '2014-06-07 17:00:00' and…
Hello lad
  • 17,344
  • 46
  • 127
  • 200
2
votes
1 answer

Joining a query result to a distinct dynamic query in SQL

I've been trying all day long to merge a simple query result of a single row to another dynamic query with a single row. To briefly explain what I've got is as follows: I have a query as a result of a complex SELECT that returns a single row of two…
sm2mafaz
  • 392
  • 3
  • 15
2
votes
1 answer

Time series in PostgreSQL - list changes

I have a table in PostgreSQL with some time-series data, showing the changes of values in a parameter - we can assume there is no gap and that each date_to has a corresponding date_from (except the last one of course): select date_from, date_to, xx…
Olivier Macchioni
  • 23
  • 2
2
votes
1 answer

PostgreSql: cannot use aggregate function in UPDATE

I have an Oracle query that I ported to PostgreSql: UPDATE "SPD_PG"."TT_SPLDR_11A2F324_29" SET "SECT_ORDER" = MAX("SECTIONS"."SECT_ORDER")+1 FROM "SPD_PG"."SECTIONS" INNER JOIN "SPD_PG"."META_SECTIONS" ON…
Teejay
  • 7,210
  • 10
  • 45
  • 76
2
votes
1 answer

MYSQL Select Records Greater Than OR First

Very simple, I have a table like: ID, Name, Key, Score I want to select all the keys from the table that has the score greater or equal than 0.5 or just the highest(if there aren't). Data example: 1, Messi, AAA, 0.8 2, Messi, AAA, 0.7 3, Ronaldo,…
Bergkamp
  • 599
  • 2
  • 6
  • 22
2
votes
1 answer

Postgresql trigger function syntax error

Hi this is my first trigger function on pgAdmin3 postgresql, I tried and have given up, I want to add a trigger to populate the item_sold table if an item has been sold. Im doing an assessment for etrading site. CREATE TRIGGER…
Vasko
  • 257
  • 3
  • 13
2
votes
1 answer

Pad arrays with NULL to maximum length for custom aggregate function

From the answer of the question How to use array_agg() for varchar[], We can create a custom aggregate function to aggregate n-dimensional arrays in Postgres like: CREATE AGGREGATE array_agg_mult (anyarray) ( SFUNC = array_cat ,STYPE …
Sarath
  • 9,030
  • 11
  • 51
  • 84
2
votes
1 answer

strange MySQL join query results with aggregate functions

i wrote the following join query to get a report using aggregate functions SELECT users.id, SUM(orders.totalCost) AS bought, COUNT(comment.id) AS commentsCount, COUNT(topics.id) AS topicsCount, COUNT(users_login.id) AS loginCount,…
Mohammad Masoudian
  • 3,483
  • 7
  • 27
  • 45
2
votes
1 answer

Mapping Aggregated Results in JDBi

Aggregation query is select min(sal) as minSal, max(sal) maxSal from Emp. I think it is redundant to create a Mapper and VO only for this use-case. Is there a predefined mapper where I can post result into a map for example exists in JDBi?
Nageswara Rao
  • 954
  • 1
  • 10
  • 32
2
votes
1 answer

Combining aggregate counts into an existing sql query

I have a sql query that I am trying to aggregate counts into, and I'm running into an issue. I've got a table that connects a user to a couple of categories they have saved. UsersToCategories. So the three tables combined would be Users, Categories…
optionsix
  • 956
  • 1
  • 8
  • 27
2
votes
1 answer

How do I use an aggregate function and select a specific column using Rails ActiveRecord?

How would I use Rails ActiveRecord to write a query similar to this? select table1.category as "Collection", count(table4.category) as "Totals" from table1 group by table1.name order by "Totals" desc The goal here is to simply report…
jktravis
  • 1,427
  • 4
  • 20
  • 36
2
votes
1 answer

Query to find all timestamps more than a certain interval apart

I'm using postgres to run some analytics on user activity. I have a table of all requests(pageviews) made by every user and the timestamp of the request, and I'm trying to find the number of distinct sessions for every user. For the sake of…
Jonathan Knight
  • 135
  • 1
  • 5
1 2 3
…
99 100