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
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
That's short for the more ...
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....
GROUP BY A.* is not allowed in SQL.
You can bypass this by using a subquery where you group by, and then join:
SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
( SELECT FKey, COUNT(foo) AS cnt
GROUP BY FKey
) AS B
ON A.PKey = B.FKey ;
There is a feature in SQL-2003 standard to allow ...
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 -...
Main problem is the missing index. But there is more.
SELECT user_id, count(*) AS ct
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 ...
In addition to @ypercube's workaround, "typing" is never an excuse for using SELECT *. I've written about this here, and even with the workaround I think your SELECT list should still include the column names - even if there are a massive number like 40.
Long story short, you can avoid typing these big lists by clicking and dragging the Columns node for the ...
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 ...
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"
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.
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:
The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans in Postgres 9.2+ and favorable conditions, see below.
SELECT m.hash, m.string, count(m.method) AS method_ct
FROM methods m
LEFT JOIN nostring n USING (hash)
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 ...
This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:
WITH partitioned AS (
number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
WHERE status = 'FREE'
counted AS (
COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
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
ON d2.category = d1.category ;
You can use the EXISTS clause:
SELECT d.ID, d.category, d.flag
1. Window functions plus subqueries
Count the steps to form groups, similar to Evan's idea, with modifications and fixes:
, min(date) AS begin
, max(date) AS end
, count(*) AS row_ct -- optional addition
SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp
SELECT date, id_type
You can see the role of this aggregate if no rows match the WHERE clause.
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 ...
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 ...
Would you believe...
SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3)
GROUP BY col1;
each row includes the first value of col2 and col3 for each unique value of col.
That assertion is not exactly true. That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this observation. ...
You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this:
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value), -- or, if Value is an int, like this, perhaps:
-- AVG(CAST(Value AS decimal(10,2))
Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
I created the table big_table according to your schema
create table big_table
updatetime datetime not null,
name char(14) not null,
I then filled the table with 50,000 rows with this code:
DECLARE @ROWNUM as bigint = 1
set @rownum = @ROWNUM + 1
insert into big_table ...
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 ...
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
group by element
select x.element, x.group_value, x.value
inner join x
on x.element = maxgv.element
and x.group_value = maxgv.maxg
This one uses RANK() function:
with grp ...
You can use GROUP BY with the GROUPING SETS () modifier:
select name, city, sum(salary) total_salary
group by grouping sets ((city, name), (city)) ;
Example of use from Microsoft Technet: Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS
GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END
ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END
ORDER BY id ASC SEPARATOR ' ') AS list_without_info
GROUP BY type ;
Tested at SQL-Fiddle: test-1
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.
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 ...
I'm going to use a simpler example where it is clear to see what the expected results are.
CREATE TABLE Queen
INSERT INTO Queen
I infer that your data looks like this:
║ PersonID ║ Name ║ Gender ║
║ 1 ║ John ║ M ║
║ 2 ║ Vicky ║ F ║
║ 3 ║ Bob ║ M ║
║ PersonID ║ JobName ║ HireDate ║
A simple and fast variant:
SELECT min(number) AS first_number, count(*) AS ct_free
SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
WHERE status = 'FREE'
GROUP BY grp
HAVING count(*) >= 3 -- minimum length of sequence only goes here
ORDER BY grp
Requires a gapless ...
group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier.
To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier.
ALTER TABLE test RENAME COLUMN sum TO "group";