83

Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression: SELECT a.id, a.name , json_agg((b.col1, b.col2, b.col3)) AS item FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; That's short for the ...


36

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms. The reasons people do it: they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code. The reasons it's bad: it's not self-documenting -...


36

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 asked you to get that phone book and read out the names: SELECT FirstName, LastName FROM dbo.PhoneBook You would usually read them out to me in last name order....


31

Main problem is the missing index. But there is more. SELECT user_id, count(*) AS ct FROM treenode WHERE project_id = 1 GROUP BY user_id; You have many bigint columns. Probably overkill. Typically, integer is more than enough for columns like project_id and user_id. This would also help the next item. While optimizing the table definition, consider ...


27

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case). You can add the columns you want ...


26

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 produce any entry for that. Outer join (right or left) will give the result of inner join and in addition all non-qualifying records from the left or right table ...


25

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; to_jsonb(b) will convert the whole row and - 'item_id' will then remove the key with the name item_id the result of that is then aggregated.


22

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options: Add the additional columns to the GROUP BY clause: GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName] Add some aggregate function on the relevant columns: ...


17

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(*) AS row_ct -- optional addition FROM ( SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp FROM ( SELECT date, id_type ...


16

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 ( SELECT DISTINCT category FROM data WHERE flag = 1 ) d2 ON d2.category = d1.category ; You can use the EXISTS clause: SELECT d.ID, d.category, d.flag ...


16

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 = 4 /*always false*/ In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case. This is a ...


15

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 clause always applies to aggregated fields, which is all remaining columns post-aggregation. You are trying to show / say that the HAVING clause is not being applied ...


13

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 ) item ) AS items FROM a; returns { id: 1, name: "thing one", items:[ { x: "child1", y: "child1 col2"}, { x: "child2", y: "child2 col2"} ] }...


13

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 the setting for work_mem. The manual: work_mem (integer) Specifies the amount of memory to be used by internal sort operations and hash tables before ...


13

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.group_value, x.value from maxgv inner join x on x.element = maxgv.element and x.group_value = maxgv.maxg ; This one uses RANK() function: with grp ...


13

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 Technet: Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS


12

I created the table big_table according to your schema create table big_table ( updatetime datetime not null, name char(14) not null, TheData float, primary key(Name,updatetime) ) I then filled the table with 50,000 rows with this code: DECLARE @ROWNUM as bigint = 1 WHILE(1=1) BEGIN set @rownum = @ROWNUM + 1 insert into big_table ...


12

Thank you for SQLfiddle and sample data! I wish more questions started this way. If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join ...


12

Consider below case: +------------+--------------+-----------+ | date | services | downloads | +------------+--------------+-----------+ | 2016-05-31 | Apps | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Apps | 1 | | 2016-05-31 | Videos | 1 | | ...


12

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 c1 20 NULL c1 30 n1 c2 20 n2 c2 50 NULL c2 70 NULL NULL 100 If you don't want the NULLs, you can replace them. The GROUPING function tells you ...


12

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 (FirstName, Surname) VALUES ('Brian', 'May'), ('Freddie', 'Mercury'), ('John', 'Deacon'), ('Roger', 'Taylor') ; Query 1 SELECT ...


11

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 Transactions corresponds to my table PortalElevators below. It has ~2000 rows. Your table TxLog corresponds to my table PlaybackStats. It has ~150M rows. It has ...


11

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 do we use Group by 1 and Group by 1,2,3 in SQL query? where he described MySQL's GROUP BY as cowboy who-knows-what-will-happen grouping. I just had to agree. ...


11

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 list> is equivalent to a <value expression> that is an arbitrary <literal>. This confirms that by * not being equivalent to an arbitrary literal ...


11

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 -> HAVING -> SELECT -> ORDER BY So you see, if we can have a WHERE filter applied before GROUP BY, we may reduce the amount of data to be processed by GROUP BY, ...


11

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 overhead of sorting that GROUP BY produces, add ORDER BY NULL


11

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: id sum ----------- ----------- 1 4


10

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 GROUP BY year If you want the totals per parameterno it should be SELECT parameterno,sum(mark),sum(maxmark) FROM table1 GROUP BY parameterno From the error ...


10

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 the concept GROUP BY. Assume we have a table like: CREATE TABLE T ( YEAR INT NOT NULL , PARAMETERNO INT NOT NULL , MARK INT NOT NULL , PRIMARY KEY (YEAR, ...


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