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

Group By and Aggregate function

I want to write an efficient query which returns a list of fruits by type, the lowest price for the type of fruit and the name of the fruit. Right now, I have a query which return me the fruit type and the lowest price for that type (see below). But…
Martin
  • 39,309
  • 62
  • 192
  • 278
2
votes
1 answer

update column with aggregate function in codeigniter

I want to change this format to codeigniter format which is like this->db->where('','') This is my code $data = "UPDATE dbhpl.hplpb SET hplpb.PEL_4 = (select COUNT(*) FROM pelayanan WHERE pelayanan.ID_AREA=hplpb.ID_AREA AND…
user5072610
  • 127
  • 2
  • 10
2
votes
3 answers

mysql use group by column in where condition

How can I make this query work : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE SUM(Hits) > 100 GROUP BY column1..... The problem is the where clause, mysql display error : Error Code : 1111 Invalid use of group function I try to…
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
2
votes
1 answer

Counts of groups

I have a table with a text column that contains various bits of data. For example Hello world This is a piece of text Q1 3 New text Q2 2 Q1 2 Q1 3 More text The bits of text trhat have Q1 at the start are pieces of voting data and are…
Dave
  • 153
  • 9
2
votes
3 answers

How do I use window function to get max price and its id?

I have this query select adate, factoryid, purchid, itemname, max(price) as price from tableb where catnum = 9 group by adate, factoryid, purchid, itemname order by adate, factoryid, purchid, itemname But I want the id for that row. So in a…
dotnetN00b
  • 5,021
  • 13
  • 62
  • 95
2
votes
0 answers

T-SQL aggregate query not working

I was trying to find an aggregate of all the packages and package items which are late based on the Ship By Date. This is what I have so far and the counts are not right at the last aggregate query. I am expecting a count of 1 for packages(PackageId…
dotnetdev_2009
  • 722
  • 1
  • 11
  • 28
2
votes
1 answer

SAS/SQL aggregate by multiple columns

I have some data that looks like this Trade ID Trade Party 1 Trade Party 2 Trade 1 Trader 1 Trader 2 100000.00 2 Trader 2 Trader 1 50000.00 3 Trader 2 Trader 3 …
user4970373
  • 71
  • 2
2
votes
3 answers

Oracle LISTAGG() for multiple attributes?

I wonder if there is a better (i.e. faster execution) solution to the problem described below. Step 1) create table t (k number, v1 number, v2 number); insert into t values (1,1,1); insert into t values (1,2,2); insert into t values (1,2,3); insert…
Lukas
  • 29
  • 1
  • 4
2
votes
2 answers

How many records created for each day of the week this year?

I have about 50k rows in a Postgres database that are users and when they signed up. I am trying to understand how many users sign up for each day of the week since the start of the year, e.g.: 1238 on Monday 3487 on Tuesday 1237 on…
Peter Clark
  • 67
  • 2
2
votes
2 answers

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT

I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server…
akw5013
  • 23
  • 4
2
votes
2 answers

mysql update table from another table

I'm trying to update a field in one table, from the sum of another field, in another table. company_tbl (PRIMARY, companySize, companyName) location_tbl (PRIMARY, companyID, locationSize, locationName) The two tables link by company_tbl.PRIMARY =…
Since_2008
  • 2,331
  • 8
  • 38
  • 68
2
votes
2 answers

SQL: left join and sum: Arithmetic overflow error converting expression to data type int

I have this query: select p.UserName, sum(b.PercentRials) as amount, sum(r.Amount) as Pays from bills b inner join UserProfiles p on b.PayerUserName=p.UserName left outer join PayReceipts r on p.UserName=r.UserName where p.[Percent]>0 and…
Mahmood Dehghan
  • 7,761
  • 5
  • 54
  • 71
2
votes
3 answers

How to update two columns in a table with an aggregate function in MSSQL?

I've tried several options but I can't find the right solution. I want to update two columns in the Products table. These columns need to be updated from another table called ProductShops. The two columns are named LowestPrice and…
Pim
  • 113
  • 1
  • 7
2
votes
1 answer

SQL-Server. Calculate formula with subquery in aggregate function

I need to calculate value of Quantity column where Name = A using this formula in SQL-Server: A(Quantity) = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E Sample data: Static Static Dynamic Name ID Quantity -- Here are more columns,…
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
2
votes
2 answers

SQL - Count grouped entries and then get the max values grouped by date

I've got a sqlite table holding every played track in a row with played date/time Now I will count the plays of all artists, grouped by day and then find the artist with the max playcount per day. I used this Query SELECT COUNT(ARTISTID) AS…
Marcus
  • 43
  • 1
  • 5
1 2 3
…
99 100