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

Control dtype of aggregation results

When I do df.groupby('id').aggregate({ "timestamp": { "len" : len, ... }, .... }) I get timestamp.len column of type datetime64 which is, obviously, not what I want. How do I control this? I can probably do some…
sds
  • 58,617
  • 29
  • 161
  • 278
2
votes
2 answers

Subquery returned more than 1 value due to where and group by

I want to make a simple view that allows the to see if the sum of stock from different warehouses are >0 and < that the minimum stock of said product. Here is the code: SELECT a.code, a.description, a.stk_min , b.Stk_Qty FROM a INNER JOIN b ON…
Hélder Filipe Correia Carneiro
  • 23
  • 5
2
votes
1 answer

Average result of last 10 rows

I'm trying to find the average figure for the last 10 rows in a database table: select avg(Reading) from Readings Order By Rowid desc limit 10; This pulls the average of all entries in the table, not the last 10. I've tried all sorts of variations…
PeteBradshaw
  • 111
  • 1
  • 5
  • 11
2
votes
1 answer

How to count results within results in MySQL?

I have a table of items which I'm getting from a few different online stores, like Ebay/Amazon, etc. Before today, I wanted to simply group each item based on the year it was made and the manufacturer who made it. So, the results would look…
BDuelz
  • 3,890
  • 7
  • 39
  • 62
2
votes
2 answers

pyspark: new column name for an aggregated field

I have the following code with some aggregation function: new_df = my_df.groupBy('id').agg({"id": "count", "money":"max"}) Then the new column I have are COUNT(id) and MAX(money). Can I specify the column names myself instead of using the default…
Edamame
  • 23,718
  • 73
  • 186
  • 320
2
votes
2 answers

(MySQL) Group by field and select both COUNT(field) and number of grouped rows

I have a many-to-many table with approximately this structure: id | obj ----+--------- 1 | 27 1 | 42 2 | 32 2 | 42 2 | 162 2 | 89 3 | 2 3 | 209 Essentially the table associates any number of objects (obj) with any number of…
Janus Bahs Jacquet
  • 859
  • 1
  • 11
  • 27
2
votes
5 answers

Return selected rows as per Max value without duplicate using MS SQL

I am using MS SQL database. I need to selected rows as per Max value without duplicate. Tried SQL QUERY given below Select CId, Rate, Max(FDateTime) from FTable Where OId = 1413 and type = 'C' Group by CId,Rate But it gives…
NIKHIL K A
  • 328
  • 5
  • 16
2
votes
1 answer

how to add two columns of two different rows have one same field in MySQL?

I have a MySQL database: results: ID | B_ID | SUM ------------ |-------------|--------- 1 | 400 | 10 2 | 500 | 20 3 | 500 | 30 4 | 400 | 40 But i…
Raza Saleem
  • 185
  • 3
  • 16
2
votes
4 answers

Why does "zero" results in COUNT appear or not when placing the condition in WHERE or LEFT JOIN?

I have read that placing conditions in WHERE or in JOIN does not matter. However, I am running into a result that sounds a bit fishy: the results differ depending on where I put the condition. Could anyone can explain its underlying logic to me? I…
fedorqui
  • 275,237
  • 103
  • 548
  • 598
2
votes
2 answers

Conditional Sum SQL

I am very new to SQL and have been presented with, what seems to me, a complex task. I have a table which is generating the number of various fruit purchased on a given day. Thus: G. A G.B 2016-06-01 Banana 45 0 …
user6089076
2
votes
1 answer

Pandas Pivot Table Keeps Returning False Instead of 0

I am making a pivot table using pandas. If I set aggfunc=sum or aggfunc=count on a column of boolean values, it works fine provided there's at least one True in the column. E.g. [True, False, True, True, False] would return 3. However, if all the…
Elliptica
  • 3,928
  • 3
  • 37
  • 68
2
votes
2 answers

MySQL query for shortest & longest length of a field

Problem I need to return the shortest & longest city names along with their respective lengths from a database. I also welcome refinements to this query to make it more elegant. SQL Fiddle I have a sample database & query thus…
techmsi
  • 433
  • 1
  • 5
  • 21
2
votes
3 answers

Simple SQL Problem

Hey...I am in a bind here. I am not much of a programmer but the guy who does the sql at my business is out sick. If I have a table like this (I am simplifying this a lot but this is where I am stuck). Name Object Payment Joe A 100 Jan…
user466334
  • 65
  • 3
2
votes
1 answer

SQL:Getting count from many tables for a user record in USER table.Whats the best approach?

I have 4 SQL server(2008 version) tables 1) USER- to store user information (Fields : UserId,UserName) 2) FILES - to store files uploaded by user (FileId,FileName,UserId) 3) PHOTOS -to store files uploaded by user (PhotoId,PhotoName,UserId) 4)…
Shyju
  • 214,206
  • 104
  • 411
  • 497
2
votes
2 answers

Mysql - Count how many rows have a certain number?

I have a query that checks a tables and groups all entries from a user and counts those entries SELECT username, count(userid) FROM orders GROUP BY userid This returns a list of username's and how many orders they have…
William
  • 1,033
  • 2
  • 13
  • 25
1 2 3
…
99 100