Questions tagged [group-by]
GROUP BY : SQL Aggregation for Common Values
742
questions
59
votes
7
answers
172k
views
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 ...
- 1,591
50
votes
6
answers
161k
views
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.
- 2,071
35
votes
2
answers
82k
views
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 ...
- 937
32
votes
3
answers
22k
views
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 ...
32
votes
1
answer
176k
views
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 ...
- 423
27
votes
3
answers
225k
views
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.[...
- 371
26
votes
4
answers
149k
views
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 ...
- 930
20
votes
6
answers
34k
views
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 ...
- 807
20
votes
6
answers
5k
views
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 ...
user39509
19
votes
7
answers
5k
views
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)
...
- 193
15
votes
4
answers
34k
views
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 | attributes
--------+-----------...
- 299
14
votes
1
answer
40k
views
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 ...
- 153
13
votes
1
answer
1k
views
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);
...
- 29.2k
13
votes
3
answers
15k
views
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); ...
- 641
13
votes
2
answers
64k
views
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 ...
- 133
12
votes
3
answers
32k
views
How to unnest and GROUP BY elements of a JSON array?
Given the band table, with a json column holding an array :
id | people
---+-------------
1 | ['John', 'Thomas']
2 | ['John', 'James']
3 | ['James', 'George']
How to list the number of bands each ...
- 303
11
votes
3
answers
5k
views
Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?
I am used to Microsoft technologies including SQL Server. Today I ran across a Q&A where the following passage from the MySQL documentation was quoted:
Standard SQL would reject your query ...
11
votes
2
answers
6k
views
GROUP BY needs ORDER BY NULL to avoid filesort
I've noticed again and again that when I EXPLAIN queries with a GROUP BY clause, I get filesort as an extra condition. A long time ago I read a suggestion to use GROUP BY NULL in these cases to avoid ...
- 726
11
votes
1
answer
5k
views
Does the SQL Spec require a GROUP BY in EXISTS ()
Microsoft currently permits this syntax.
SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
SELECT *
FROM ( VALUES (1),(1) )
AS t(x)
WHERE g.x = t.x
HAVING count(*) > 1
);
Notice ...
- 63.1k
10
votes
2
answers
11k
views
WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?
I'm in the process of rewriting queries that no longer pull in all the required data. My question is in regard to a practice I've never seen and haven't found any question on StackExchange that ...
- 351
10
votes
2
answers
32k
views
select rows having condition met for group (without temporary table)
Having the table with 3 columns:
ID category flag
1 A 1
2 A 0
3 A 0
4 B 0
5 C 0
I want to select all rows that have flag = 1 at least ...
- 225
10
votes
2
answers
27k
views
Postgres: Use result of group by as column names
I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many ...
- 997
10
votes
2
answers
21k
views
Should I group by and then join, or join and then group by?
(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables)
Assume three tables t1, t2 and t3 with a pairwise 1:n ...
- 263
9
votes
3
answers
81k
views
Select rows based on latest date with multiple joins
I have this query (SQLFiddle):
SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.rtime,
u.user_name,
s.status_name
FROM company c
LEFT JOIN action a ON a.company_id=c.id
LEFT JOIN user u ON u.id=a....
- 187
9
votes
2
answers
21k
views
Why can I select all fields when grouping by primary key but not when grouping by another column
How is this a valid statement (where id is the primary key of the table):
select * from table group by id ;
and this is not:
select * from table group by name ;
ERROR: column "pgluser.id" must ...
- 799
9
votes
2
answers
18k
views
PostgreSQL: Generate a series of dates for each group in a table
I have a balances table in PostgreSQL 9.3 that looks like this:
CREATE TABLE balances (
user_id INT
, balance INT
, as_of_date DATE
);
INSERT INTO balances (user_id, balance, as_of_date) VALUES
(...
- 331
9
votes
1
answer
68k
views
GROUP BY and ORDER BY problem
I have two tables like this:
CREATE TABLE cmap5 (
name varchar(2000),
lexemes tsquery
);
and
CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn (
cid int NOT NULL, -- ...
- 101
8
votes
2
answers
10k
views
GROUP BY with MAX versus just MAX
I am a programmer, dealing with a big table which the following scheme:
UpdateTime, PK, datetime, notnull
Name, PK, char(14), notnull
TheData, float
There is a clustered index on Name, UpdateTime
I ...
- 821
8
votes
1
answer
49k
views
Multiple "GROUP_CONCAT"s with WHERE clause
In a table as
id name type info
1 BMW car yes
2 Reno car no
3 IBM electronics no
4 Sony electronics yes
5 Mazda ...
- 4,521
8
votes
1
answer
433
views
GROUPING SETS returning unexpected results with calculated expression
Here we have two similar queries using grouping sets
where the SELECT clause contains some expressions calculated in aggregation:
SELECT RN10, RN10 / 10, COUNT(*) FROM
(
SELECT RN, RN/10 AS ...
- 11k
8
votes
3
answers
22k
views
group_concat and group by together
I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell.
Here's how my data is...
table1
+...
- 83
8
votes
1
answer
608
views
Is it possible to get seek based parallel plan for distinct/group by?
An example from this question shows that SQL Server will choose a full index scan to solve a query like this:
select distinct [typeName] from [types]
Where [typeName] has a non-clustered, non-unique ...
- 2,120
8
votes
2
answers
49k
views
How group with min(date) and select an another column in the same table
I have a query who return something like it :
Name Gender Job date of hire
John M mechanic 2012-05-08
John M electrician 2010-01-01
Vicky F scientific 2012-11-11
Bob ...
- 193
8
votes
1
answer
45k
views
Error "column does not exist" in a SELECT with JOIN and GROUP BY query
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id (...
- 183
8
votes
1
answer
38k
views
Select when group by contains more than one row with the same value?
I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many ...
- 765
7
votes
3
answers
10k
views
Stop SQL Server from ordering results? [duplicate]
I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server ...
- 2,819
7
votes
3
answers
19k
views
GROUP BY gives wrong result with MIN() aggregate function
I have a table that looks like this:
+------+--------+----------+------+--------+-------+------+------------+
| id | code | category | mq | weight | weave | show | min(price) |
+------+--------+...
- 183
7
votes
2
answers
18k
views
MySQL: Using DISTINCT and GROUP BY together?
I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:
SELECT DISTINCT user_id, post_id, post_content
FROM some_table
GROUP BY post_id, user_id
HAVING ...
7
votes
3
answers
43k
views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number ...
- 4,521
7
votes
3
answers
36k
views
How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?
I have the following schema:
CREATE TABLE Person (
PersonId int PRIMARY KEY
)
CREATE TABLE Action (
ActionId int PRIMARY KEY,
PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId),
...
- 363
7
votes
1
answer
129k
views
Grouping data by MONTH on DATETIME column in SQL Server
I have a table Employee which has the columns Id, Date of joining and Name
Date of joining is a DATETIME column.
I want to know how many users have joined in the month of October?
- 217
7
votes
5
answers
25k
views
Get only overall summary WITH ROLLUP and GROUP BY for multiple fields
Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group, as well as the overall summary:
CREATE TABLE test (name VARCHAR(50), number TINYINT);
INSERT INTO ...
- 223
7
votes
3
answers
30k
views
Getting around the constraint "column must appear in the GROUP BY clause or be used in an aggregate function"
I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function.
Lets say I'm modelling peoples' cars, ...
- 258
6
votes
5
answers
2k
views
Return NULL when distinct values else return value
Below is an example of the type of data I get (collected by different users):
name
surname
Moe
Momo
Moe
Momo
Jack
JAJA
Jack
Jacky
I would like to find when two users have collected different ...
- 163
6
votes
4
answers
383
views
SQL Challenge - The Sensor Threshold Exception Report
I've added a solution without using window functions and a benchmark with a large data set below Martin's Answer
This is a followup thread to GROUP BY using columns not in the SELECT list - when is ...
- 4,098
6
votes
1
answer
6k
views
Improving sort performance in GROUP BY clause
I have two tables in Postgres 9.4.1 events and event_refs with the following schemas:
events table
CREATE TABLE events (
id serial NOT NULL PRIMARY KEY,
event_type text NOT NULL,
event_path ...
- 285
6
votes
3
answers
2k
views
Finding contiguous ranges in grouped data
I have a table with the following structure:
CREATE TABLE `Rings` (
ID_RingType CHAR(2),
Number MEDIUMINT UNSIGNED,
ID_User INT(11)
);
and with data:
INSERT INTO `Rings` VALUES
('AA',...
- 324
6
votes
3
answers
45k
views
Use column alias in GROUP BY
I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly?
SELECT
KOS_VER_ID AS "...
- 61
6
votes
2
answers
2k
views
T-SQL Purpose of MAX in this Group By Query
I have run into some pre-existing SQL that I'm having a hard time uderstanding.
SELECT
MAX(I.Symbol) Symbol
, MAX(I.Ticker) CUSIP
, MAX(I.Name) Name
, SUM(H.Quantity) TotalQuantity
, SUM(...
- 218
6
votes
2
answers
36k
views
how to calculate difference of first row and last row fields value in each group
I have table with structure like this:
+-------+------------------+
| Value | Date |
+-------+------------------+
| 10 | 10/10/2010 10:00 |
| 11 | 10/10/2010 10:15 |
| 15 | 10/10/...
- 229