SQL Subquery column equals operation
Try this query select u.ID, u.Name, case when p.value>0 then 'True' else '' end as IsPermissionEnabled from Users u left join permission p on p.UserID = u.ID and p.key='CanEdit' ...
T-SQL Ordering a Recursive Query - Parent/Child Structure
sql,tsql,recursion,order,hierarchy
The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....
How to delete only the table relationed
When creating a foreign key constraint, you can also decide what should happen with the constraints. For instance, if you want to delete all articles when a category is deleted (as I guess is your question): Schema::table('articulos', function($table) { $table->foreign('categoria_id')->references('id')->on('categorias')->onDelete('cascade'); $table->foreign('creador_id')->references('id')->on('users'); }); Also see documentation. The cascade identifies to the...
creating stored procedure in mysql calculate profit from product table
mysql,sql,database,stored-procedures
You forgot the () after the procedure name. Also, you should set a delimiter: DELIMITER // CREATE PROCEDURE sp_profit() BEGIN SET @v1:= (select sum( cost_price * current_stock) from product); SET @v2:= (select sum( selling_price * current_stock) from product); SELECT (@v2 - @v1); END; // ...
Problems With FOR XML AUTO
sql,asp.net,sql-server,subquery,sqlxml
Change XML PATH('') to XML PATH('tag')
Purging Database - Count purged/not-purged tables
mysql,sql,sql-server,database,stored-procedures
The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...
Title search in SQL With replacement of noice words [on hold]
sql,sql-server,sql-server-2008
I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...
Count Rows On a Condition in SQL SERVER 2000
Remove the type from the group by and move the case inside the sum(): Select a.department, count(emp_id) as TotalPresent, sum(case when a.Type = 'Employee' THEN 1 ELSE 0 END) as Employee, sum(case when a.Type = 'Officer' THEN 1 ELSE 0 END) as Officer from AttendanceLog m INNER JOIN EmployeeData a...
Using Sum in If in Mysql
Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...
left join table, find both null and match value
Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...
The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument
sql,sql-server,sql-server-2008
You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...
Recursive Lag Column Calculation in SQL
Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...
Update where column has highest value
That should give you the latest start_date to every entry which has the same value in element like the defined value in update statement. So you need to change it only at one place. UPDATE [table] t SET t.end_date = NULL WHERE t.element = 1 AND t.start_date = (select max(sub.start_date)...
Cant delete in database because of constraints
Best way to do it is by using a stored proceed rather than a sql statement in C# code. You are getting error because the referenced records are still present in referenced table and are using cmd.ExecuteReader(); rather than cmd.ExecuteNonQuery();. So you need to delete records for DBS2_MOVIE WHERE MOVIE_ID...
SQL Multiple LIKE Statements
sql,sql-server,tsql,variables,like
WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...
Select count Columns group by No
this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...
Retrieve data from one table and insert into another table
INSERT INTO tbl2 ( Name ,parentId ) SELECT DISTINCT manager ,0 FROM tbl1 WHERE manager NOT IN ( SELECT employee FROM tbl1 ) INSERT INTO tbl2 SELECT DISTINCT employee ,0 FROM tbl1 UPDATE tbl2 SET parentid = parent.id FROM tbl2 INNER JOIN tbl1 ON tbl2.Name = tbl1.employee INNER JOIN tbl2...
Foreign key in C#
You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...
SQL Customized search with special characters
sql,sql-server,sql-server-2008
Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...
copy table and drop it
sql,sql-server,sql-server-2008,tsql,stored-procedures
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...
how can i use parameters to avoid sql attacks
I think the only solution is create a new function and gradually migrate to it. Public Function ExecuteQueryReturnDS(ByVal cmdQuery As SqlCommand) As DataSet Try Dim ds As New DataSet Using sqlCon As New SqlConnection(connStr) cmdQuery.Connection = sqlCon Dim sqlAda As New SqlDataAdapter(cmdQuery) sqlAda.Fill(ds) End Using Return ds Catch ex As...
oracle sql error Case When Then Else
Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...
How to Retrieve value from a UDT
It's quite simple. A table type just can be used inside it's database. You try to access it like that: [test].[dbo].[tvf_id] Instead try this: [dbo].[tvf_id] As your error already stated, you exceed the maximum of allowed prefixes. 1 is allowed (dbo) and you use two (test + dbo). For further...
Get unique row by single column where duplicates exist
SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...
echo both users
Why don't you just do it in one single query? Just replace the necessary table and column name, and variables/values/parameters to be bind in your query: $query = mysqli_query($conn, "SELECT first_name, last_name, description, role FROM `wp_usermeta` WHERE `first_name` = '$first_name' OR `last_name` = '$last_name' OR `description` = '$description' OR `role`...
One identifier for set of values
sql,sql-server,sql-server-2008,sql-server-2008-r2
You can solve this for example using the DENSE_RANK() function. See the example below: CREATE TABLE #yourTable(col1 int, col2 int) INSERT INTO #yourTable(col1,col2) VALUES(null, 72),(null, 72),(null, 72),(null, 33),(null, 33),(null, 12),(null, 12),(null, 55),(null, 72) SELECT * FROM #yourTable -- Your part: UPDATE yt SET col1 = numbering.number FROM #yourTable yt INNER...
Looping distinct values from one table through another without a join
sql,sql-server,tsql,while-loop
So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...
In PostgreSQL is it possible to join between table and function?
Your table uses a carid value to retrieve the corresponding part_ids from function PartsPerCar() which returns a set of rows, a so-called table function. In sub-section 5 (keep on reading) we see that Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions...
Conversion failed when converting date and/or time from character string in sql server
Too many zeros in the millisecond part. This works fine DECLARE @Value nVarchar(MAX)='2016-03-01 00:00:00.000' SELECT CONVERT(DATETIME,@Value,101) You can try using LEFT like this DECLARE @Value nVarchar(MAX)='2016-03-01 00:00:00.000000' SELECT CONVERT(DATETIME,LEFT(@Value,23),101) ...
Get items with 0 Counts after GroupBy in SQL
Building on the second half of Gordon's answer, you also need to add the condition of your WHERE clause to the join between Days and VendorBillUVBLog. with days as ( select cast(getdate() as date) as dd union all select cast(getdate() - 1 as date) as dd union all select cast(getdate()...
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=
You are clearly using SQL Server syntax, so use TOP 1: (select TOP 1 [FileName] from [dbo].[APP_FinishProductCompleteForm_Attachment] where [finishProductID][email protected] and FileType='3' ) FileName3, (select TOP 1 [FileName] from [dbo].[APP_FinishProductCompleteForm_Attachment] where [finishProductID][email protected] and FileType='4' ) FileName4, (select TOP 1 [FileName] from [dbo].[APP_FinishProductCompleteForm_Attachment] where [finishProductID][email protected] and FileType='5' ) FileName5, (select TOP 1...
C# No value given for one or more required parameters
c#,sql,syntax,ms-access-2007,helper
Your column name creditcardNum missing t Command.CommandText = "UPDATE Table1 SET Username='"+textBox10.Text+"' , Email='"+textBox9.Text+"' , FirstName='"+textBox8.Text+ "' , LastName='"+textBox7.Text+"' , CrediCardNum='"+textBox6.Text+"' Where Username='"+m[0]+"'" ; it should be Command.CommandText = "UPDATE Table1 SET Username='"+textBox10.Text+"' , Email='"+textBox9.Text+"' , FirstName='"+textBox8.Text+ "' , LastName='"+textBox7.Text+"' , CreditCardNum='"+textBox6.Text+"' Where Username='"+m[0]+"'" ; Your table definition picture saying...
How to join result of a Group by Query to another table
You have mistake here from tblUsers u,a.Login_Name try to move this piece of code a.Login_Name to select Select u.User_Id, a.Login_Name from tblUsers u inner join (SELECT s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name) a on u.Login_Name=a.Login_Name ...
Create a unique URL for linking to data pulled from a database [closed]
javascript,html,sql,google-maps,maps
If you don't want to do anything on the server to provide a unique url, you can use a client side hash with urls in the form /mymap#uniqueId. The hashed part of the url is accessible in javascript like so: window.location.hash You can even set that property on the location...
Newbie to linking SQL tables
Better to add ID in you Calender table so your schema should be something like this Note : in EmployeeDate table you dont need to add FirstName or LastName because you have EmployeeID which references to Employee table where you can easy get those fields. And your query will be...
SQL Group By multiple categories
Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...
How to incorporate wildcards with place holder variables in Java using SQL
In order to make this work, the wild card cannot be applied to the place holder variable. Instead it needs to be added around searchParameters.get("formation"). See example below. if (!Strings.isNullOrEmpty(searchParameters.get("formation"))) { nativeQueryFromAndWhereClause.append(" AND formation LIKE :formation "); parameters.put("formation", "%" + searchParameters.get("formation") + "%"); }"); ...
Entity Framework code-first: querying a view with no primary key
sql,oracle,entity-framework,view,ef-code-first
It's not possible in Entity Framework to have Entities without primary key. Try to get a possible unique key from the views, combining columns, ... to create a unique primary key. If is not possible there is a workaround, if is only a queryable view, with out need to do...
Groovy: run SQL SELECT LIKE from file with params
try this SELECT * FROM [db].[dbo].[TABLE] WHERE [ID] LIKE '%'+convert(varchar(50),:id)+'%' ...
SQL: overcoming no ORDER BY in nested query
Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ...
Retrieve Values As Column
If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...
SQL average age comparison function returns null
While you declared the variable averMen, you have not initialized it. The query which should be calculating averMen is calculating averWomen instead. Try changing ... SELECT AVG(DATEDIFF(BIRTH_DATE,CURDATE())) INTO averWomen FROM PLAYERS WHERE sex = 'M'; into SELECT AVG(DATEDIFF(BIRTH_DATE,CURDATE())) INTO averMen FROM PLAYERS WHERE sex = 'M'; ...
pivot not working in SSIS
That statement is equivalent to: select listid, max(case when [order] = 1 then val end) as [1] from gmt_listsvals group by listid ...
How to get entries from SQL that only appear in certain value?
You can select all Transmission = 'Inventory' ids and filter out those exist in Transmission in('Starting', 'Stopping'): select distinct(DeviceID) from YourTable WHERE Transmission = 'Inventory' and DeviceID not in ( select distinct(DeviceID) from YourTable WHERE Transmission in('Starting', 'Stopping') ); SQL Fiddle: http://sqlfiddle.com/#!9/81896/12...
Select all none null values from all columns in a table
select @variable will just return you a value of variable. You need to use some dynamic SQL I believe. Maybe smth like exec('select ' + @colname + ' from ' etc) will work for you (at least it will work in MS SQL server).
Error 1136: Column dosent match value count at row 1
remove '[email protected]' INSERT INTO cliente (CFCL, CognomeCL, NomeCL , SessoCL , ComuneNascitaCL, DataNCL, IndirizzoCL, TelefonoCL, CittadinanzaCL, PatenteCL, DataSCL) VALUES ('MNA12OSQWDEWEWO8', 'Cognome', 'nome', 'F', 'Abbasanta', '1995-07-07', 'via pisa, 21', '0803597845', 'italiana', 'ba1234567q', '2020-07-07'); ...
SQL Server: Issues with data type
Type of expression in SUM determines return type. Try the following: SELECT pa.[type], (SUM(CAST(pa.Actions_Logged as BIGINT ))/SUM(CAST(pi.Impressions_Served as BIGINT ))) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; ...
JOIN on keys that don't have the same value
So in this case, I used a subquery to get the initial results, and then used a join. SELECT table1.nisinfo.* FROM table1.nisinfo JOIN (SELECT distinct(fqhn) FROM table2.hosts WHERE package = 'bash') AS FQ ON ((SUBSTRING_INDEX(FQ.fqhn, '.', 1)) = table1.nisinfo.shortname); ...
Scrambling(randomize) row id's in mysql table
How about this? alter table add column randorder double; update table set randorder = rand(); create index idx_table_randorder on table(randorder); Then you can "shuffle" the records by doing: select t.* from table t order by randorder; This will use the index so it will be fast. It is stable, so...
Pull information from SQL database and getting login errors
change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...
Extracting XML data from CLOB
Use xmltable. Data setup: create table myt( col1 clob ); insert into myt values('<ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" CurrencyId="43"/> </FoodItemDetails> <BillOption> <BillDetails TotalPrice="22222" BillOption="cash"/> </BillOption> <Authoritativeness/> </ServiceDetails>' ); commit; Query:...
Improving work with SQL DataTime
You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....
How to SUM two first character in MySQL?
Try this (If you want to sum two first characters): SELECT SUM(CAST(Substr(period, 1, 1)) AS int) + CAST(Substr(period, 2, 1)) AS int)) FROM table GROUP BY period or SELECT SUM(CAST(Substr(Period, 1, 2) AS int)) FROM table GROUP BY period ...
Updating entity framework model using Linq
You need to update your SQL server with the following columns: UserActivity_UserID, Orders_UserId. Or in the code remove this two columns(Map again your DB to the edmx file)....
Dynamic Query Generation is not working with SQL
ALFKI is being treated as if it is a column name. Your generated sql will look like this: select * from Customers where CustomerID = ALFKI What you want is your code to look like this: select * from Customers where CustomerID = 'ALFKI' To achieve this, change your where...
mysql_real_escape_string creates \ in server only not in local
Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...
Is it possible to index views in Apache Solr
You can use the data import handler and set the "query" to point to your view. See example below: <dataConfig> <dataSource driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:/temp/example/ex" user="sa" /> <document name="products"> <entity name="feature" query="SELECT * FROM MyView"> <field column="attr_1" name="Attr1" /> <field column="attr_2" name="Attr2" /> <field column="attr_3" name="Attr3" /> </entity> ...
Default the year based on month value
SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...
Is there a better way to write this query involving a self select?
sql,postgresql,join,aggregate-functions
Query The query is not as simple as it looks at first. The shortest query string does not necessarily yield best performance. This should be as fast as it gets, being as short as possible for that: SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT...
SQL only one cell from column can have X value
Have a separate table with one row that contains the mainOfferName of the row that you want. Then query as: select yt.*, (nt.mainOfferName is not null) as valueFlag from yourtable yt left join newtable nt on yt.mainOfferName = nt.mainOfferName; When you want to change the value, change the value in...
How to select next row after select in SQL Server?
The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...
How to order SQL query result on condition?
sql,postgresql,order,condition
Use CASE expression in ORDER BY clause: SELECT category FROM ( SELECT DISTINCT category FROM merchant ) t ORDER BY CASE WHEN category = 'General' THEN 0 ELSE 1 END, category ASC CASE guarantees that rows with General will be sorted first. The second argument orders the rest of the...
SQL result table, match in second table SET type
Example of junction/intersect table usage. create table subscription_plans ( id int not null auto_increment primary key, -- common practice name varchar(40) not null, description varchar(255) not null, price decimal(12,2) not null -- additional indexes: ); create table pricing_offers ( id int not null auto_increment primary key, -- common practice name...
timestamp SQL to Excel
The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...
Executing dynamically created SQL Query and storing the Query results as a temporary table
You have two solutions for this: As a first solution you can simply use an INSERT EXEC. This will work if you have a specified result set. This could be used if your procedure just returns one result set with a fixed result design. Simply create your temporary table with...
update a table from another table using oracle db
Try this instead: UPDATE product SET provider_name = ( SELECT p.name FROM provider p WHERE p.provider_id = product.provider_id ); ...
Convert AWK command to sqlite query
SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...
Select Statement on Two different views
Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...
Fastest way to find different rows in two mysql tables
SELECT * FROM Tab2 WHERE NOT EXISTS (SELECT 'x' FROM Tab1 where Tab1.ID= Tab2.ID) I think this query would give you a little faster result....
SQL varchar variable inserts question mark
there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...
Latest two datetime in SQL Server
With row_number function: with cte as(select *, row_number() over(partition by event_id order by event_timestamp desc) rn from events) select * from cte where rn <= 2 ...
Sql inner join with three tables
Based on the schema you are providing, I will assume that you find all the products of each document based on 1. which document_group the document is in 2. which product_type the document_group is associated with If that is the case, this is what your query would look like: SELECT...
Matplotlib: Plot the result of an SQL query
Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...
postgresql complex group by in query
SELECT itemid, deadlineneeded, sum(quantity) AS total_quantity FROM <your table> WHERE (deadlineneeded - delievrydate)::int >= 1 GROUP BY 1, 2 ORDER BY 1, 2; This uses a "delievrydate" (looks like a typo to me) that is at least 1 day before the "deadlineneeded" date, as your sample data is suggesting....
Query how often an event occurred at a given time
This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...
Database object with different data
sql,asp.net,asp.net-mvc,database,entity-framework-6
Ideally what you want is a many-to-many relationship between your Shop and Product entities: public class Shop { public int ShopId {get; set;} public virtual ICollection<ShopProduct> ShopProducts {get; set;} } public class Product { public int ProductId {get; set;} public string Name {get; set;} public virtual ICollection<ShopProduct> ShopProducts {get; set;}...
How to join 2 tables with select and count in single query
With your sample data, you don't even need the Person table -- because you seem to have redundant table in the two tables. You should probably fix this, but: select pl.id, pl.name, count(*) from personline pl group by pl.id, pl.name; Your count is just counting all the rows from the...
Fastest way to add a grouping column which divides the result per 4 rows
sql,sql-server,tsql,sql-server-2012
Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...
How to use subquery result as the column name of another query
Use your sub query as an inline table. Something like.... select item, item_type, .. decode(fore_column_name, 'foo', 1, 2) * 0.9 as finalforcast, decode(fore_column_name, 'foo', 1, 2) * 0.8 as newforcast from sales_data, ( select fore_column_name from forecast_history where ... ) inlineTable I'm assuming here that the value from the sub-query...
Can't output Guid Hashcode
Well, are you looking for a hashcode like this? "OZVV5TpP4U6wJthaCORZEQ" Then this answer might be useful: Guid g = Guid.NewGuid(); string GuidString = Convert.ToBase64String(g.ToByteArray()); GuidString = GuidString.Replace("=",""); GuidString = GuidString.Replace("+",""); Extracted from here. On the linked post there are many other useful answers. Please take a look! Other useful links:...
Can't find FULLTEXT index while using IN BOOLEAN MODE
mysql,sql,full-text-search,mariadb,match-against
There could be many several factors why in boolean mode not working like mysql version you are having, storage engine etc. However its highly recommended to use fulltext index when you are doing match .. against since this is what it is meant for. Now coming to the point will...
Add 1 to datediff result if time pass 14:30 or 2:30 PM
Could be: SELECT reservations.customerid, DateDiff("d",reservations.checkin_date, Date()) + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights FROM reservations ...
insert and update sql statement in single sql statement
Not sure how about other SQL servers, but you are allowed to execute multiple queries using the same syntax in MS SQL. using (SqlConnection connection = CreateConnection()) { SqlCommand command = connection.CreateCommand(); command.CommandText = @"INSERT INTO `TableA` VALUES (1, 2, 3); UPDATE `TableB` SET [Val] = 'value' WHERE [Id] >...
Merging two tables into new table by ID and date
You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...
like and regexp_like
sql,regex,oracle,oracle11g,regexp-like
Try this one: SELECT * FROM employee WHERE REGEXP_LIKE (fname, '^pr(*)'); Fiddle This one also seems to work as far as I can tell: SELECT * FROM employee WHERE REGEXP_LIKE (fname, '^pr.'); Or another one that works: SELECT * FROM employee WHERE regexp_like(fname,'^pr'); ...
How can I create missing date records for each employee based off a limited calendar?
Create a table that contains all the possible dates in the range, @alldates Then insert your missing records with something like this: Query INSERT INTO dbo.timeclock SELECT id ,d.punchtime ,'no time entered' FROM ( SELECT DATE ,id FROM @alldates d CROSS JOIN ( SELECT DISTINCT id FROM dbo.timeclock )...
MySQL: Select several rows based on several keys on a given column
If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...
Can someone explain to me how this statement is an exclude?
I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...
select: result based on occurrence of explicit value
Write a subquery that gets the number of occurrences of each weight, and join with this. Then you can test the number of occurrences to decide which field to put in NewPrice. SELECT f.*, IF(weight_count = 1, Price, ReducedPrice) AS NewPrice FROM fonts AS f JOIN (SELECT weight, COUNT(*) AS...
Inner Join 3 tables pl/sql
The issue is that you are using the alias C where you should not, with the count function. This: C.Count(C.column6) should be: Count(C.column6) and the same change applies in the order by clause (which might be counting the wrong column - should it not be column6?): order by C.count(column5) desc...