Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

According to Wikipedia.com

The GROUP BY clause is used to project rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.

Depending on the Dialect of SQL, modifiers and functions can be applied to GROUP BY.

Functions can include: MAX(), MIN(), COUNT(), AVG(), SUM(), etc.

Other Definitions Provided by

746 questions
60
votes
7 answers

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the JSON object? I have read about ROW, but it returns a…
Yanick Rochon
  • 1,651
  • 4
  • 20
  • 28
50
votes
6 answers

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
ursitesion
  • 2,061
  • 8
  • 32
  • 45
37
votes
2 answers

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how many nodes I do the following (added explain…
tomka
  • 967
  • 1
  • 10
  • 16
34
votes
1 answer

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT empname,array_agg(phonenumber) AS phonenumbers FROM…
Programmer
  • 443
  • 2
  • 5
  • 6
33
votes
3 answers

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT(B.foo) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table with 40 columns and I would like to avoid listing each…
Anonymous Maximus
  • 779
  • 2
  • 8
  • 16
28
votes
3 answers

How to select multiple columns but only group by one?

I have a problem with group by, I want to select multiple columns but group by only one column. The query below is what I tried, but it gave me an error. SELECT Rls.RoleName,Pro.[FirstName],Pro.[LastName],Count(UR.[RoleId]) as [Count] from…
Nayeem Mansoori
  • 381
  • 1
  • 4
  • 7
26
votes
4 answers

How to get a group where the count is zero?

I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero. For this I've tried this query: Create table Streets( ID int IDENTITY …
H. Pauwelyn
  • 930
  • 6
  • 18
  • 35
21
votes
6 answers

Find "n" consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ASSIGNED 1 000004 FREE 1 000005 FREE 1 …
boobiq
  • 827
  • 3
  • 9
  • 13
20
votes
6 answers

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a…
user39509
20
votes
7 answers

Form groups of consecutive rows with same value

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp , id_type integer ) ; INSERT INTO tmp (date, id_type) VALUES ( '2017-01-10 07:19:21.0', 3 ), (…
Lelo
  • 303
  • 2
  • 6
15
votes
4 answers

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | …
reox
  • 299
  • 1
  • 3
  • 10
15
votes
1 answer

Why is this stream aggregate necessary?

Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script): SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = (SELECT 1); Note: the "AND 1 = (SELECT 1) is just to keep this…
Josh Darnell
  • 30,133
  • 5
  • 70
  • 124
14
votes
1 answer

get only rows with max group value

for example, we have: element | group_value | value a | 1 | 2000 a | 2 | 1500 a | 2 | 2500 b | 1 | 1000 I'd like to return only last 3 records as those are the records with max group…
spuppis
  • 153
  • 1
  • 1
  • 5
13
votes
2 answers

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of work they've performed and is stored in the…
farewelldave
  • 133
  • 1
  • 1
  • 7
13
votes
3 answers

Get percentage of group by

I have this data: CREATE TABLE tickets(user_id int NOT NULL); INSERT INTO tickets VALUES (1); INSERT INTO tickets VALUES (2); INSERT INTO tickets VALUES (3); -- 3 times INSERT INTO tickets VALUES (4); -- 10 times Now I want to display the…
Mio
  • 651
  • 1
  • 11
  • 23
1
2 3
…
49 50