37
votes
Stop SQL Server from ordering results?
Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It's organized by last name, first name.
If I ...
- 42.5k
28
votes
Select columns inside json_agg
Starting with 9.6 you can simply use - to remove a key from a JSONB:
SELECT a.id, a.name, jsonb_agg(to_jsonb(b) - 'item_id') as "item"
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
...
- 78.4k
28
votes
Accepted
How to get a group where the count is zero?
The reason your query did not work as intended:
Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not ...
- 12.9k
19
votes
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
Consider below case:
+------------+--------------+-----------+
| date | services | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Apps | 1 |
| 2016-05-...
- 191
19
votes
Accepted
get only rows with max group value
First answer uses a CTE to select max(group_value) and then join with the table.
with maxgv as
(
select element, max(group_value) maxg
from x
group by element
)
select x.element, x....
- 23.3k
18
votes
Form groups of consecutive rows with same value
1. Window functions plus subqueries
Count the steps to form groups, similar to Evan's idea, with modifications and fixes:
SELECT id_type
, min(date) AS begin
, max(date) AS end
, count(*...
- 167k
17
votes
Accepted
WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?
The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.
The problem here is in how you are describing what the HAVING clause applies to. The HAVING ...
- 67.7k
17
votes
Accepted
Why is this stream aggregate necessary?
You can see the role of this aggregate if no rows match the WHERE clause.
SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1
AND 1 = 1 /*To avoid auto parameterisation*/
AND Id%3 =...
- 81.3k
16
votes
Accepted
select rows having condition met for group (without temporary table)
GROUP BY cannot be used alone because it only returns 1 row per group (category).
You can use a sub query with flag = 1 and INNER JOIN:
SELECT d1.ID, d1.category, d1.flag
FROM data d1
INNER JOIN (
...
- 10.1k
14
votes
Accepted
Improving sort performance in GROUP BY clause
work_mem
That's what makes your sort expensive:
Sort Method: external merge Disk: 3904kB
The sort spills to disk, which kills performance. You need more RAM. In particular, you need to increase ...
- 167k
13
votes
Select columns inside json_agg
You can actually do it without group by, using subqueries
SELECT
a.id, a.name,
(
SELECT json_agg(item)
FROM (
SELECT b.c1 AS x, b.c2 AS y
FROM b WHERE b.item_id = a.id
...
- 231
13
votes
Accepted
sql server getting total for each group
You can use GROUP BY with the GROUPING SETS () modifier:
select name, city, sum(salary) total_salary
from Employee
group by grouping sets ((city, name), (city)) ;
Example of use from Microsoft ...
- 96.2k
13
votes
Accepted
GROUP BY needs ORDER BY NULL to avoid filesort
MySQL 5.7 Reference Manual / ... / SELECT Syntax
If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the ...
- 18.9k
12
votes
How SELECT column does not list in GROUP BY clause
It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of ...
- 22.8k
12
votes
sql server getting total for each group
WITH ROLLUP would come close to what you want:
select name, city, sum(salary) total_salary
from Employee
group by city, name WITH ROLLUP
This would give you:
Name City Salary
n1 c1 10
n2 ...
- 11.6k
12
votes
Accepted
GROUPING SETS returning unexpected results with calculated expression
I'm going to use a simpler example where it is clear to see what the expected results are.
CREATE TABLE Queen
(
FirstName VARCHAR(7),
Surname VARCHAR(7)
);
INSERT INTO Queen
...
- 81.3k
11
votes
Accepted
Is it possible to get seek based parallel plan for distinct/group by?
I have exactly the same set up and I've been through the same stages of rewriting the query.
In my case the table names and meaning is a bit different, but overall structure is the same. Your table ...
- 4,577
11
votes
Accepted
How SELECT column does not list in GROUP BY clause
You can not select aggregates across a field if you don't include the field in the group by list.
If you want the totals per year you should write
SELECT year,sum(mark),sum(maxmark)
FROM table1
...
- 15.6k
11
votes
Accepted
Why can I select all fields when grouping by primary key but not when grouping by another column
id is a primary key.
As far as I remember, this is actually a legal query according to ANSI/ISO SQL.
Grouping by primary key results in a single record in each group which is logically the same ...
- 3,196
11
votes
Accepted
GROUP BY gives wrong result with MIN() aggregate function
As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior.
As example, Aaron Bertrand answered the post Why ...
- 179k
11
votes
Accepted
Does the SQL Spec require a GROUP BY in EXISTS ()
I found it in the SQL 2011 spec...
If the <select list> “*” is simply contained in a <table subquery> that is immediately contained in an <exists predicate>, then the <select ...
- 60.5k
11
votes
WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?
Solomon gives very good explanations, but to me, the easy answer is to remember the SQL query logical processing order as Itzik Ben-Gan wrote here
The sequence is always
FROM -> WHERE -> GROUP BY -> ...
- 2,943
11
votes
Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?
Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query
This is correct, up to 1992.
But it is plainly ...
10
votes
How to get a group where the count is zero?
This is one possible way.
select s.name as streets,
(select count(*)
from users
where StreetID = s.id) cnt
from streets s;
- 23.3k
10
votes
Accepted
Query monthly having group_concat and group by need help
Terminology and Methodology
This kind of transformation – rows to columns – is called pivoting. It is typical to pivot data simultaneously with their aggregation, as seems to be a requirement in your ...
- 22.5k
10
votes
Accepted
How to unnest and GROUP BY elements of a JSON array?
The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More:
...
- 167k
10
votes
Accepted
Sum up counts in subquery
Does this do what you want?
SELECT
total =
SUM(x.records)
FROM
(
SELECT
records =
COUNT_BIG(thecol)
FROM thetable
WHERE thecol IS NOT NULL
GROUP ...
- 37.6k
10
votes
Accepted
Why is GROUP BY clause missing rows
UNION excludes duplicate rows.
You want to use UNION ALL in this example:
SELECT id, SUM(num) as sum
FROM (
SELECT 1 AS id, 2 AS num UNION ALL
SELECT 1, 2 /*!*/) AS a
GROUP BY id
Results:...
9
votes
Accepted
How does TOPN influence a query with group by?
My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.
Speaking from a SQL Server perspective, it depends. Here is an ...
- 80.3k
Only top scored, non community-wiki answers of a minimum length are eligible