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
1 answer

SQL - JOIN AN AGGREGATE TABLE WITH A NONAGGREGATE TABLE

I have written the following code that selects the aggregate remaining interest payments from each loan: SELECT a.PARENTREF, a.INTEREST - Isnull(b.INTEREST,0) AS INTEREST FROM (SELECT PARENTREF, SUM(TOTAL) AS INTEREST FROM LG_011_BNCREPAYTR …
SQLfreaq
  • 141
  • 2
  • 11
2
votes
3 answers

select data group by distinct date and get first and last data in the group

I am not sure if this is doable, i have a table of data with timestamp e.g. apple 2013-01-02 06:24:22 banana 2013-01-12 11:08:22 apple 2013-01-02 15:24:22 apple 2013-01-02 12:30:16 banana 2013-01-14 18:34:22 i want to: rearrange the…
onegun
  • 803
  • 1
  • 10
  • 27
2
votes
1 answer

Calculate sum of two sums

Is there a way for me to calculate the sum of two sums? Take the following query (just an example query) how can I get a value for combined_total? when I run my query it says total1 is an Unknown column. Is there a way to get that value without…
Get Off My Lawn
  • 34,175
  • 38
  • 176
  • 338
2
votes
0 answers

R - Aggregate 3-Hourly Block Data into Weekly (Melt)

I am trying to compute max, min, and mean from Global Circulation Models (GCM) for the US. The data is in 3-hour blocks for 2026-2045 and 2081-2100. Sample Data: tmp1 <- structure(list(FIPS = c(1001L, 1003L, 1005L), X2026.01.01.1 = c(285.5533142, …
shouro
  • 31
  • 7
2
votes
3 answers

SQL SUM restricted

I have a view pulling together data from a number of tables. Let's consider it to be like this: TransPK Amount Currency_ID ------------------------------- 1 2000 0 1 -2000 0 2 3600 1 2 -7200 …
skiaddict1
  • 523
  • 7
  • 19
2
votes
1 answer

Calculate average from JSON column

I have a table with a column of JSON data that I want to extract information from. Specifically I just want to get the average value. Example of what I have: id speed_data 391982 [{"speed":1.3,"speed":1.3,"speed":1.4,"speed":1.5... 391983 …
Ellis Valentiner
  • 2,136
  • 3
  • 25
  • 36
2
votes
2 answers

Remove duplicate column after SQL query

I have this query but I'm getting two columns of houseid: How do I only get one? SELECT vehv2pub.houseid, vehv2pub.vehid, vehv2pub.epatmpg, dayv2pub.houseid, dayv2pub.trpmiles FROM vehv2pub, dayv2pub WHERE vehv2pub.vehid >= 1 AND…
nodebase
  • 2,510
  • 6
  • 31
  • 46
2
votes
1 answer

SQL Raw to mysql query

I need to execute the following query: select Clients.Zip, AVG(SpentT) as Average_Spent_Per_Zipcode from (select CLIENT.CLIENT_ID AS ClientID, Averages.Spent AS SpentT, CLIENT.ZIPCODE AS Zip from CLIENT, (select CLIENT_ID,…
Brandon
  • 55
  • 1
  • 5
2
votes
2 answers

Why MySQL BIT_OR() is returning different value than PHP bitwise operation

I have the following data in a MySQL table "Data Dump" 2 phone_calls 001 2 phone_calls 010 2 phone_calls 100 2 phone_calls 1000 2 phone_calls 10000 2 phone_calls 100000 if I run PHP code to do bitwise Or operation…
Jaylen
  • 39,043
  • 40
  • 128
  • 221
2
votes
2 answers

Postgresql method for finding the slope of a line and forcing through origin

I have a temporary table with 2 numeric columns, Y and X. CREATE TEMP TABLE findslope(y numeric,x numeric); Which I then populate with the desired X and Y for the line I'm trying to fit a least squares best fit line which I am currently using the…
Jeff
  • 53
  • 6
2
votes
6 answers

SQL to COUNT items associated with individuals

I have a table (ClassEnrolments) which contains student data: StudentId Student Module Status EndDate Credits 12345678 J Bloggs Introduction Pass 2014/09/01 10 12345678 J Bloggs …
SB1987
  • 23
  • 5
2
votes
3 answers

Optimizing percentage calculation SQL query

I have a table like this: ------------------ sl no | Error type ------------------ 1 | err1 ------------------ 2 | err2 ------------------ 3 | err1 ------------------ 4 | err2 ------------------ 5 | err2 ------------------ 6 …
Abhijeet
  • 11,872
  • 5
  • 22
  • 24
2
votes
2 answers

PDO MYSQL nested COUNT breaks my query

I have query like this: $query = $con->prepare("SELECT `Id`, (SELECT count(`Id`) FROM `images` WHERE `parentId` = :recId) as `numImgs` …
ministe
  • 543
  • 1
  • 5
  • 17
2
votes
3 answers

How to round a rollup SQL Query?

How can I do a round on a rollup like I round or sum a function? ROUND(SUM(T.TICKET_PRICE),2) for example: SELECT EmpId, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY EmpId, Yr WITH ROLLUP
questionToStack
  • 21
  • 1
2
votes
3 answers

SQL: Computing sum of all values *and* a sum of only values matching condition

Suppose I fetch a set of rows from several tables. I want to know the total sum of values in column x in these rows, as well as sum of only those values in x where the row satisfies some additional condition. For example, let's say I fetched: X …
user319799
1 2 3
…
99 100