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.
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
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
Using ROW_NUMBER() function in WHERE clause
To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function’s results in the where clause. WITH MyCte AS ( select employee_id, RowNum = row_number() OVER ( order by employee_id ) from V_EMPLOYEE ORDER BY Employee_ID ) SELECT employee_id FROM MyCte … Read more
ERROR: function … does not exist and HINT: No function matches the given name and argument types
Your function has a couple of smallint parameters. But in the call, you are using numeric literals that are presumed to be type integer. A string literal or string constant (‘123’) is not typed immediately. It remains type “unknown” until assigned or cast explicitly. However, a numeric literal or numeric constant is typed immediately. The … Read more