spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL » Page 1
Sum two counts in a new column without repeating the code
You may get the two individual counts of a same table and then get the summation of those counts, like bellow SELECT (SELECT COUNT(a) FROM X )+ (SELECT COUNT(b) FROM X ) AS C

Categories : SQL

Creating multiple sum fields based upon criteria in another field in MS access 2007
One method is to use conditional aggregation. In MS Access, that would look like: select valuedate, sum(iif(transactioncode = '35', amount, 0)) as trans_35, sum(iif(transactioncode = '20' or transactioncode = '85', amount, 0)) as trans_20_85 from tablelikethis where branchcode = '99' group by valuedate;

Categories : SQL

Grouping by two values in same table
You have to COUNT DISTINCT user ids (and ORDER BY ... DESC if you want the provided result): SELECT ship_type, COUNT(DISTINCT userid) as cnt FROM db1.MessageType5 GROUP BY ship_type ORDER BY cnt DESC See this fiddle.

Categories : SQL

MS SQL last record
SQL server uses TOP instead of LIMIT to restrict number of records. Your query becomes: Select top 1 sum(spareparts), month(calculationdate) from cz_axnmrs_calculations where CASE_ID in (select case_id from cz_axnmrs_cases where insurer_memberid = 'MM-O-5B57274F') and YEAR(calculationdate)='2014' group by month(calculationdate) DESC

Categories : SQL

Find field existance in more than one group of fields
Under normal circumstances, you would just do: select col1, col2, count(*) from table t group by col1, col2 having count(*) > 1; You make it sound like you want distinct values in columns 3 and 4. Some databases support: select col1, col2, count(distinct col3, col4) from table t group by col1, col2 having count(distinct col3, col4) > 1; In other databases, it is just easiest to concat

Categories : SQL

how to put an img into sql table?
I do not recommend you to insert any image into your db tables. If you do it anyway, you will looe remarkable performance you acquire from your current database, that means loss of speed and lots of useless data space. I recommend you to upload your images to your website and insert their location into your tables. here is more information about my suggestion: http://bit.ly/1rolVMX

Categories : SQL

A field referencing two tables - Foreign Key Conflict
It looks like a bad design. Why don't have one table for all children and some mark - is it boy or girl? Also I really doubt you need ToyOwnerBoyOrGirl field - as it can be easily obtained by join from toys to owners. Consider following scheme: Table Children ID Name Is_Boy Table Toys ID Name Owner_ID In this case you need just foreign key from toys to owners, and other tasks you might encoun

Categories : SQL

Comparing two Access tables identical in structure but not data
Join on all four of the fields which make up the primary key. SELECT t1.CenterName, t1.BuildingName, t1.FloorNo, t1.RoomNo, t2.CenterName FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.CenterName = t2.CenterName AND t1.BuildingName = t2.BuildingName AND t1.FloorNo = t2.FloorNo AND t1.RoomNo = t2.RoomNo WHERE t2.CenterName Is Nu

Categories : SQL

How to parse a comma separated string in SQL query if the values themselves contain a comma
You have to somehow specify which comma to ignore. So May be enclose them in double quotes? If so the below query could work for you. Sample Input : Suresh,"Rajesh,Ramesh" Output : Suresh Rajesh Ramesh Query: with my_data(names) as ( select 'Mahesh' as str from dual union all select '"Vignesh,Ganesh"' from dual union all select 'Suresh,"Rajesh,Ramesh"' from dual union all s

Categories : SQL

SQL - returning average number of items by days of the week.
This should solve your problem. I don't have SQL Server instance at hand (so can't validate it), but I guess you get the idea. WITH OrdersPerDay AS ( SELECT CAST(orderDt as INTEGER) as OrderDay, COUNT(1) as OrderCount FROM PARTNER_WORKORDER GROUP BY CAST(orderDt as INTEGER) WHERE statusNo = 110 ) SELECT DATEPART(dw, CAST(OrderDay as DATETIME)) as WeekDay, AVG(OrderCoun

Categories : SQL

How to return multiple values from a subquery
See Here Is it possible for a subquery to return two values? You need to have the subquery in the FROM clause to do this.

Categories : SQL

Improve performance of first query
Sometimes moving an "WHERE x IN" into a JOIN can improve performance significantly. Try this: SELECT foo3_beleg.id, ... FROM foo3_beleg b INNER JOIN foo3_text t ON (t.beleg_id = b.id AND t.content @@ 'footown'::tsquery) WHERE foo3_beleg.belegart_id IN ('...', ...); Here's a repeatable experiment to support my claim. I happen to have a big Postgres database handy (30 million rows)

Categories : SQL

SSDT - Is there any way to log deployment history?
There's not an option for this directly, but you can do it through a post-deploy script. If you have any sort of automation running, you can also log your deploys through that. You might look at Jamie Thomson's example for this: http://sqlblog.com/blogs/jamie_thomson/archive/2010/10/19/maintaining-version-history-in-your-database-using-visual-studio-2010.aspx

Categories : SQL

LISTAGG: group within group
Do two levels of aggregation: SELECT storeId, LISTAGG(ProductCategory || ':' || '(' || ProductIds || ')', ', ') WITHIN GROUP (ORDER BY ProductCategory) as ProductsAndCategories FROM (SELECT StoreId, ProductCategory, LISTAGG(ProductId, ',') WITHIN GROUP (ORDER BY ProductId) as ProductIds FROM SUPERMARKET GROUP BY StoreId, ProductCategory ) s GROUP B

Categories : SQL

SQL code subtraction
SELECT s.RouteID, (MAX(p.Capacity) - p.Capacity) AS Numbofavailableseats FROM Plane p INNER JOIN Schedule s ON p.PlaneSerial = s.PlaneSerial INNER JOIN Reservation r ON s.RouteID = r.RouteID WHERE s.RouteID = 'F1000' AND s.FlightDate = '10/10/2014'

Categories : SQL

Update table records with accumulated result
In SQL Server 2012+, you can do this with a cumulative sum: select Id, TimeUse, IdCustomer, Created, (case when sum(timeuse) over (partition by idcustomer order by id) < 10 then 0 else timeuse end) as timecalc from table t; You can do the same thing in earlier versions using outer apply or a subquery. If you want an update, just use a CTE: with toupdate as (

Categories : SQL

select inside select doesn't work in hive
Your query is quite strange. Why not just write: select user_key, (sum(333) / 10) as calculationResult from user_usage_table group by user_key; This should work in both Oracle and hive.

Categories : SQL

PostgreSQL 9.3 - How to insert using json_populate_recordset but still get auto ids by sequence
Usually, when you want to insert into a table that has a column of type serial or bigserial, you omit that column from the list of columns. I tried to change your original syntax as little as possible to show how this works. -- I made no changes to the CTE, except to cut some lines. WITH input AS ( SELECT '{ "tablename_a":[{"a_id":1,"b_id":2,"c_id":3}, {"a_id":2,"b_id":51,"c_id":3}]

Categories : SQL

Optimise SQL query: find items that are geographically touching, but without association
Try forcing Oracle to use nested loops instead of hash joins like this: select /*+ USE_NL(s r)*/s.id as id, r.id as unconnected_route_id from structure s, route r WHERE s.batch_number = '%{batch_number}' and r.batch_number = '%{batch_number}' and SDO_ANYINTERACT(s.geometry, r.geometry) = 'TRUE' and not exists ( select /*+ NL_AJ(feature_connectivity)*/

Categories : SQL

SQL-Oracle: Difficulties in resolving basic problems, part2
Something like this? SELECT last_name, TO_CHAR(hire_date,'DD-MON-YYYY') AS "HIRE_DATE" FROM employees a JOIN (Select department_id from employees where last_name = :surname) b on a.department_id = b.department_id and last_name <> :surname EDIT The only problem with this type of solution is that if there are two people with the same surname in different departments, so it might be useful

Categories : SQL

SQL Server 2012 - Best way to assign incrementing number for differing sets of records, from a specific value
Use the row_number analytic function: insert into MasterBookings (CustomerNo, Sequence_Number, ExternalBookingID) select CustomerNo, ExternalBookingID, row_number() over (partition by CustomerNo order by ExternalBookingID) + ( select coalesce(max(Sequence_Number), 0) from MasterBookings where MasterBookings.CustomerNo = NewBookings.CustomerNo ) as Sequence_Number from NewBo

Categories : SQL

MS SQL Server - convert(datetime, string, code) -> What code for dd/mm/yyyy hh:mm:ss (24hours)?
convert(datetime,'08/12/2014 16:46:12',13) OR convert(datetime,'08/12/2014 16:46:12',113) One Last GO :) SELECT CONCAT ((convert(datetime,'08/12/2014,103)),' ',(convert(datetime,'16:46:12',114))) AS DateTime Here is a list of them all http://www.w3schools.com/sql/func_convert.asp

Categories : SQL

Left Join with duplicate keys in the right table
SQL tables are inherently unordered, so there is no such thing as a "first" key. In most databases, you can do something like this: with t2 as ( select t2.*, row_number() over (partition by EMPnum order by id) as seqnum from t2 ) select * from t1 left join t2 on t1.EMPnum = t2.EMPnum and t2.seqnum = 1; Here id is just any column that specifies the ordering. If none

Categories : SQL

Joining multiple tables in SQL Server
When you are sure you can fix it in 2 levels, you could use someting like this: select Rate from ExchangeRates where SellingCurrency = 'SEK' and BuyingCurrency = 'EUR' UNION select er1.Rate*er2.Rate as Rate from ExchangeRates er1 left join ExchangeRates er2 on er2.SellingCurrency = er1.BuyingCurrency where er1.SellingCurrency = 'SEK' and er2.BuyingCurrency = 'EUR'

Categories : SQL

Round off to the smallest integer value
The CEILING function returns the smallest integer greater than or equal to the specified numeric expression. The FLOOR function returns the largest integer less than or equal to the specified numeric expression. For example, in considering a numeric expression of 12.9273, CEILING returns 13 and FLOOR returns 12. The return value of both FLOOR and CEILING has the same data type as the input numeric

Categories : SQL

How to drop materialized views using EXECUTE statement in PostgreSQL
The error message indicates that you have non-standard names created with double-quoting, like "Dimension" (mixed case). You need to quote and escape identifiers properly in dynamic SQL. Not only because it doesn't work any other way, also to avoid SQL injection. Plus, you may have to schema-qualify names. Details: Table name as a PostgreSQL function parameter Also, you could drop multiple MV'

Categories : SQL

Sql database on geo redundant storage in Azure
Geo-redundant storage writes to the geo-replica asynchronously. There is no loss of performance. In case the primary data center is lost you can read a consistent but out of date snapshot of your data from the secondary if you chose to enable that option.

Categories : SQL

How to select the last 3 rows but the last one
SELECT TOP 2 FROM ( SELECT TOP 3 doesntmatterwhat FROM whatever ) a ORDER BY doesntmatterwhat Not using TOP SELECT n.doesntmatterwhat FROM (SELECT n.doesntmatterwhat, row_number() OVER (ORDER BY date DESC) AS sequence FROM whatever n ) n WHERE n.sequence>= 2 AND n.sequence<= 3;

Categories : SQL

sql server status change time
Minutes SELECT NUMBER, MAX([SYSMODTIME]) AS Closed, MIN([SYSMODTIME]) AS Open, (DATEDIFF (mi, MAX([LoginDateTime]), MIN([SYSMODTIME]))) AS [datediff] FROM table GROUP BY NUMBER Hours SELECT NUMBER, MAX([SYSMODTIME]) AS Closed, MIN([SYSMODTIME]) AS Open, (DATEDIFF (hh, MAX([LoginDateTime]), MIN([SYSMODTIME]))) AS [datediff] FROM table GROUP BY NUMBER

Categories : SQL

SQL joins in Entity Framework (WPF MVVM)
eliminates the method you have in your DbContext and takes a methodo of type List. pública static List<Price> FindAll() { using (var context = new PriceContext()) { var sqlQuery = from a in context.products join b in context.prices on a.id equals b.productid select a; } return sqlQuery;

Categories : SQL

SQL query select on one criteria if second criteria is false
SELECT DISTINCT ID, Status FROM table WHERE ID NOT IN (SELECT ID FROM table WHERE Status = 'Fail') AND Status = 'Pass' Small comment: AND Status = 'Pass' is not required in this case because there are only two Statuses and if the first condition is true the second one will also be true (tautology) but I decided to leave it for better indication. It works.

Categories : SQL

Creating a view that consist column with static info
CREATE VIEW TEST AS ( SELECT a.ID, a.E_ID, b.CAT1 as C1, c.CAT1 as C2, 'NL' AS add1, 'RS' AS add2 FROM RECP a JOIN ART b on (a.ID=b.SKU) JOIN ART c on (E_ID=c.SKU) )

Categories : SQL

SQL Server - Include table name programatically
To do something like that you either have to make some rather complex and large query into the system views (sys.tables and sys.columns) But you're properly better off building the query dynamically in what ever code lanuage you use and execute it as a string: A very rough example could be something like DECLARE @tableName varchar(255) = 'tbl1'; DECLARE @tableName2 varchar(255) = 'tbl2'; DECLAR

Categories : SQL

Find Modified/New/Deleted Records Between Two Tables
You can use the OUTPUT clause: Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable. See the the follow

Categories : SQL

Accessing Azure SQL Database from Access (without port 1433)
You could fire up a Azure VM with SQL Server already installed. Then you should be able to change the ports to your liking. Check out this post... http://roberteichenseer.wordpress.com/2012/07/06/nderung-default-port-sql-server-innerhalb-einer-azure-vm/

Categories : SQL

Function with dates as params that can be NULL
You have to define the function as returning a set of sampletest and then return different query restults based on the arguments passed. CREATE FUNCTION test_dob_dates(_sd date, _ed date) RETURNS SETOF sampletest AS $BODY$ BEGIN IF _sd IS NOT NULL AND _ed IS NOT NULL THEN RETURN QUERY SELECT * FROM sampletest WHERE dob BETWEEN _sd AND _ed; ELSIF _sd IS NULL AND _ed I

Categories : SQL

Convert SQL file to XML to upload sample data in wordpress
Joel - I think no need to convert sql to xml. you can directly export your blog data from your wordpress admin as per below : Tools -> Export -> All content It will give you data in xml format.so this file you can import anywhere in other wp sites.

Categories : SQL

What is the best way to find string containing a sequence of identical digits in SQL? (fake phones search)
Update: To find recurring numbers, you can use a function like this. It returns 1 if any of the characters in @text are used at least @min times consecutively. create function IsRecurring(@text varchar(255), @min int) returns int as begin declare @i int = len(@text) - @min + 1 declare @result int = 0 while @i > 0 begin if replace(substring(@text, @i, @min), substring(@text,

Categories : SQL

Parse JSON into Oracle table using PL/SQL
I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it. The following script inspired by Oracle Community answer worked for me DECLARE l_param_list VARCHAR2(512); l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; l_response_text VARCHAR2(32767); l_list json_list; A_id VARCHAR2(200); UserId VARCHAR2(100); UserName

Categories : SQL


Recently Add
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
Find field existance in more than one group of fields
how to put an img into sql table?
A field referencing two tables - Foreign Key Conflict
Comparing two Access tables identical in structure but not data
How to parse a comma separated string in SQL query if the values themselves contain a comma
SQL - returning average number of items by days of the week.
How to return multiple values from a subquery
Improve performance of first query
SSDT - Is there any way to log deployment history?
LISTAGG: group within group
SQL code subtraction
Update table records with accumulated result
select inside select doesn't work in hive
PostgreSQL 9.3 - How to insert using json_populate_recordset but still get auto ids by sequence
Optimise SQL query: find items that are geographically touching, but without association
SQL-Oracle: Difficulties in resolving basic problems, part2
SQL Server 2012 - Best way to assign incrementing number for differing sets of records, from a specific value
MS SQL Server - convert(datetime, string, code) -> What code for dd/mm/yyyy hh:mm:ss (24hours)?
Left Join with duplicate keys in the right table
Joining multiple tables in SQL Server
Round off to the smallest integer value
How to drop materialized views using EXECUTE statement in PostgreSQL
Sql database on geo redundant storage in Azure
How to select the last 3 rows but the last one
sql server status change time
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.