Skip to main content
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 ...
Brent Ozar's user avatar
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 ...
SqlWorldWide's user avatar
  • 13.5k
22 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-...
imVJ's user avatar
  • 321
20 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....
McNets's user avatar
  • 23.8k
19 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 =...
Martin Smith's user avatar
  • 85.4k
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(*...
Erwin Brandstetter's user avatar
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 ...
Solomon Rutzky's user avatar
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 ( ...
Julien Vavasseur's user avatar
14 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 ...
redben's user avatar
  • 241
14 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: ...
Erwin Brandstetter's user avatar
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 ...
ypercubeᵀᴹ's user avatar
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 ...
Akina's user avatar
  • 20.1k
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 ...
Lennart - Slava Ukraini's user avatar
12 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 ...
David דודו Markovitz's user avatar
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 ...
RDFozz's user avatar
  • 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 ...
Martin Smith's user avatar
  • 85.4k
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 ...
Tom V's user avatar
  • 15.7k
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 ...
RolandoMySQLDBA's user avatar
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 ...
Evan Carroll's user avatar
  • 63.9k
11 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;
McNets's user avatar
  • 23.8k
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 -> ...
jyao's user avatar
  • 3,073
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
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 ...
Andriy M's user avatar
  • 23.1k
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 ...
Erik Darling's user avatar
  • 42.1k
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:...
LowlyDBA - John M's user avatar
9 votes

SQL Server: Order of Rows

This article by Conor Cunningham (Software Architect, SQL Server Engine at Microsoft) should answer your questions: No Seatbelt – Expecting Order without ORDER BY Some extracts below: The hard part ...
Scott Hodgin - Retired's user avatar
9 votes

SQL Server: Order of Rows

under what circumstances does SQL SERVER order the result even if unasked? This might happen if the access path to retrieve the data happens to produce data already ordered e.g. an index scan in ...
Martin Smith's user avatar
  • 85.4k
9 votes

Include NULL row on join

Use RIGHT JOIN instead of INNER JOIN right after your comment in the code: Limit table_A to only records with data from transactions_with_children, but I also need to include a null row for all ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible