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 (...
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).
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...
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 ...
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...
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 =...
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...
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...
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...
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...
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...
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...
How to design a history for n:m relations
What about this commonly used model? create table cross_ref ( a_id references a , b_id references b , from_ts timestamp , to_ts timestamp , primary key (a_id, b_id, from_ts) ); (NB I used timestamp as you did; normally I would use date)...
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)....
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...
converting varchar to Int SQL SERVER
The error is simply occurring as your destination column can only have one data type. The first part of your CASE statement is effictively setting the column type to expect an integer, so when you hit the ELSE section and try to insert Not Same, you're getting the error. Sample:...
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....
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...
Group and order by a column but donot include that column in results
Move the column from SELECT clause to ORDER BY clause: SELECT `newel_inventoryKeywordIdDictionaryId`.`inventoryId` FROM `newel_inventoryKeywordIdDictionaryId` , `newel_inventoryDictionary` WHERE `newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId` AND ( `newel_inventoryDictionary`.`word` = 'alabaster' OR `newel_inventoryDictionary`.`word` = 'chess' ) GROUP BY inventoryId ORDER BY COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) DESC; ...
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 ...
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...
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...
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 ...
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...
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 ...
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:...
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...
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...
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 ); ...
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 ...
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....
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 ...
How do I find records in one table that do exist in another but based on a date?
select d.`name` from z_dealer d where (select count(*) from z_order o WHERE o.promo_code = d.promo_code AND o.date_ordered > '2015-01-01') = 0 ...
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...
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;}...
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,...
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'); ...
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...
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...
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 )...
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] >...
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 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...
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...
Groovy: run SQL SELECT LIKE from file with params
try this SELECT * FROM [db].[dbo].[TABLE] WHERE [ID] LIKE '%'+convert(varchar(50),:id)+'%' ...
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'; ...
Take thousand value in SQL
SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...
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:...
for-loop add columns using SQL in MS Access
sql,ms-access,table,for-loop,iteration
I have tested your code, I do not see any issues except for the fact, your For statement is a bit off and that you needed to set the db object. Try this code. Sub toto() Dim db As Database, i As Integer Set db = CurrentDb For i =...
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...
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...
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...
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 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...
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...
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'); ...
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...
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 ...
Using Sum in If in Mysql
Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...
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...
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...
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...
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> ...
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...
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; ...
Problems With FOR XML AUTO
sql,asp.net,sql-server,subquery,sqlxml
Change XML PATH('') to XML PATH('tag')
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 ...
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()...
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...
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' ...
SQL Repeated Condition in Two Tables
sql,variables,coding-style,condition
you can achieve it like this DECLARE @AccountId int; @AccountID=20; DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE [email protected]); DELETE FROM Table_B WHERE [email protected]; ...
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. ...
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...
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....
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...
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)...
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`...
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....
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); ...
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...
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';...
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...
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...
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...
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 ...
Return 0 in sum when no values to sum - sql server
sql,sql-server,sql-server-2008-r2,sum
You need a table of all the statuses. If you don't already have a table, you can do this in the query itself: SELECT ClientDeliveryStatus, COUNT(t.ClientDeliveryStatus) AS Total FROM (SELECT 'Past Due' as cds UNION ALL SELECT 'Due Tomorrow' UNION ALL SELECT 'Due Today' UNION ALL SELECT 'Due Beyond' )...
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") + "%"); }"); ...
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...
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; // ...