Cast the datetime to a date, then GROUP BY using this syntax: SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate); Or you can GROUP BY the alias as @orlandu63 suggested: SELECT SUM(foo), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly; Though I don’t think it’ll make any difference to performance, it is a little clearer.
finally done with GROUP BY DATEPART(YEAR, DT.[Date]), DATEPART(MONTH, DT.[Date]), DATEPART(DAY, DT.[Date]), DATEPART(HOUR, DT.[Date]), (DATEPART(MINUTE, DT.[Date]) / 10)
LIKE and the equality operator have different purposes, they don’t do the same thing: = is much faster, whereas LIKE can interpret wildcards. Use = wherever you can and LIKE wherever you must. SELECT * FROM user WHERE login LIKE ‘Test%’; Sample matches: TestUser1 TestUser2 TestU Test
ROUND ( 123.456 , 2 , 1 ) When the third parameter != 0 it truncates rather than rounds. Syntax ROUND ( numeric_expression , length [ ,function ] ) Arguments numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. length Is the precision to … Read more
count(*) counts NULLs and count(column) does not [edit] added this code so that people can run it create table #bla(id int,id2 int) insert #bla values(null,null) insert #bla values(1,null) insert #bla values(null,1) insert #bla values(1,null) insert #bla values(null,1) insert #bla values(1,null) insert #bla values(null,null) select count(*),count(id),count(id2) from #bla results 7 3 2
A unique constraint can’t be over 8000 bytes per row and will only use the first 900 bytes even then so the safest maximum size for your keys would be: create table [misc_info] ( [id] INTEGER PRIMARY KEY IDENTITY NOT NULL, [key] nvarchar(450) UNIQUE NOT NULL, [value] nvarchar(max) NOT NULL ) i.e. the key can’t … Read more
While not strictly nested, you can use common table expressions to reuse previous queries in subsequent ones. To do this, the form of the statement you are looking for would be WITH x AS ( SELECT * FROM MyTable ), y AS ( SELECT * FROM x ) SELECT * FROM y
See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html ALTER TABLE tbl_name ALTER COLUMN col_name TYPE varchar (11);
The “user is currently connected to it” might be SQL Server Management Studio window itself. Try selecting the master database and running the ALTER query again.
PostgreSQL also supports full POSIX regular expressions: select * from table where value ~* ‘foo|bar|baz’; The ~* is for a case insensitive match, ~ is case sensitive. Another option is to use ANY: select * from table where value like any (array[‘%foo%’, ‘%bar%’, ‘%baz%’]); select * from table where value ilike any (array[‘%foo%’, ‘%bar%’, ‘%baz%’]); … Read more