Generating a histogram from column values in a database
SELECT COUNT(grade) FROM table GROUP BY grade ORDER BY grade Haven’t verified it, but it should work.It will not, however, show count for 6s grade, since it’s not present in the table at all…
sql
How to output oracle sql result into a file in windows?
Use the spool: spool myoutputfile.txt select * from users; spool off; Note that this will create myoutputfile.txt in the directory from which you ran SQL*Plus. If you need to run this from a SQL file (e.g., “tmp.sql”) when SQLPlus starts up and output to a file named “output.txt”: tmp.sql: select * from users; Command: sqlplus … Read more
MySQL: Finding rows that don’t take part in a relationship
Here’s a typical way to do this query without using the subquery method you showed. This may satisfy @Godeke’s request to see a join-based solution. SELECT * FROM movies m LEFT OUTER JOIN seen s ON (m.id = s.movie_id AND s.user_id = 123) WHERE s.movie_id IS NULL; However, in most brands of database this solution … Read more
postgres – comparing two arrays
figured it … there’s an && operator http://www.postgresql.org/docs/current/static/functions-array.html “&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1]”
INSERT INTO TABLE from comma separated varchar-list
Something like this should work: INSERT INTO #IMEIS (imei) VALUES (‘val1’), (‘val2’), … UPDATE: Apparently this syntax is only available starting on SQL Server 2008.
Selecting/casting output as integer in SQL
SELECT CAST(sum(number)/count(number) as UNSIGNED) as average, date FROM stats WHERE * GROUP BY date
drop trigger if exists and create
The [name] field in sys.objects will contain only the actual name (i.e. trg), not including the schema (i.e. dbo in this case) or any text qualifiers (i.e. [ and ] in this case). AND, you don’t specify the table name for DROP TRIGGER since the trigger is an object by itself (unlike indexes). So you … Read more
ALTER TABLE ADD CONSTRAINT gets Error 1064
Omit the parenthesis: ALTER TABLE User ADD CONSTRAINT userProperties FOREIGN KEY(properties) REFERENCES Properties(ID)
STRING_AGG aggregation result exceeded the limit of 8000 bytes error
Try as below select c.id , c.bereichsname , STRING_AGG( CAST(j.oberbereich as nvarchar(MAX)),’,’) oberBereiches from stellenangebote_archiv j join bereiche c on j.bereich_id = c.id group by c.id, c.bereichsname So the problem is the length of the concatenated string is exceeding the character limit of the result column. So we are setting the limit to max by … Read more
How to efficiently check if a table is empty?
You are looking for an indication if the table is empty. For that SQL has the EXISTS keyword. If you are doing this inside a stored procedure use this pattern: IF(NOT EXISTS(SELECT 1 FROM dbo.MyTable)) BEGIN RAISERROR(‘MyError’,16,10); END; IF you get the indicator back to act accordingly inside the app, use this pattern: SELECT CASE … Read more