Tag Info

New answers tagged

0

Why does the DISTINCT clause level all the queries results down to 3,074? Based on your question, your table would have data similar to this for any Agent (A1). Agent | InvoiceID A1, 1 A1, NULL A1, NULL A1, 2 A1, 245 A1, 3 Based on this data, a query to get agents which have invoice id which are not null would be SELECT Agent FROM [AgentCommTMP] ...


0

You will be able to do it like this: select jsn_extract(reading, '$.key_fiyat') from table where key_il = 33; in the new version of MySQL( http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/). For now it's just a lab release though.


3

It's possible for a SELECT statement to modify the database, for example: select mysequence.nextval increments a sequence select myfunction executes a PL/SQL function which could have any sort of side effects The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as ORA-14551: cannot perform a ...


1

PROPOSED QUERY SELECT B.firstname,B.lastname FROM ( SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,COUNT(1) rcount FROM names GROUP BY CONCAT(lastname,LEFT(firstname,1)) HAVING COUNT(1) > 1 ) A INNER JOIN ( SELECT CONCAT(lastname,LEFT(firstname,1)) nametag, firstname,lastname FROM names ) B USING (nametag); YOUR SAMPLE DATA ...


2

The following is your common denominator, which, not seeing the data and assuming I am reading your question correctly, would have a count of 3074 rows. SELECT DISTINCT [AC2].[Agent] FROM [AgentCommTMP] AS [AC2] WHERE [AC2].[InvoiceID] IS NOT NULL which is different from the following which has the potential to have more rows. SELECT ...


3

You explain: I get result 1453 only when all items in column attributeY are empty for 1453. But that's incorrect. Bold emphasis mine. The aggregate function count returns (per documentation): number of input rows for which the value of expression is not null The same is true for SQLite (per documentation): The count(X) function returns a ...


1

You are just missing the GROUP BY YOUR QUERY WITH GROUP BY SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID GROUP BY student.StudentID,student.`Name`; SAMPLE DATA DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh; USE ...


2

It works for me with a similarly created structure. You didn't provide DDL and INSERTs for your table so I just made up a shorter one that should have the same effect: $ sqlite3 test.db SQLite version 3.8.8.3 2015-02-25 13:29:11 sqlite> CREATE TABLE test(x integer, y text); sqlite> INSERT INTO test(x, y) VALUES (1, ''); sqlite> INSERT INTO test(x, ...


0

The specification provided in the MySQL documentation gives most of the information on what abilities each privilege type enable, but it needs to be supplemented by other sources to get a comprehensive picture of what each privilege level allows. Here are some examples from Managing and Using MySQL, pp. 100-102, that are not apparent from the docs: ...


2

SELECT IF(A01 = 99999, NULL, A01) AS A01, IF(A02 = 99999, NULL, A02) AS A02, ... FROM ... (For empty string, use '' instead of NULL.)


2

You could unpivot the data. I'm going to add an surrogate ID column to your table because this will make reconciliation easier. The unpivoted data will look like this: id SourceCol SourceVal 1 A 3 1 B 3 1 C 3 ... 1 N 3 2 A 5 2 B 5 2 C 5 ... 2 N 5 Then you group by ID and ...


3

SELECT column_a ,column_b ,column_c ,column_d FROM mytable WHERE column_a = column_b AND column_a = column_c AND column_a = column_d ETC. SQL Fiddle Link My Notes CREATE TABLE mytable ( column_a int, column_b int, column_c int, column_d int ); INSERT INTO mytable VALUES (1,2,4,7); INSERT INTO mytable VALUES (6,3,5,6); INSERT INTO mytable ...


1

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options: Update to MySQL 5.6 and use a FULLTEXT index Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB. Convert to a MyISAM table, or create a spare MyISAM table that you can ...


0

It's inconvenient sometimes, but it's SQL standard behavior, and it prevents ambiguities. You cannot reference column aliases in the same SELECT list. There are shorter syntax options: SELECT s.*, s.percent_water * 100 AS percent_water_100 FROM ( SELECT id, wet_weight / NULLIF(dry_weight - 1, 0) AS percent_water FROM samples ) s; And you can ...


0

I have an idea that MAY cause it: The tables you are showing us are not SELECT * FROM ... are they? I think that in dtr_tbl, the logdate has a time part that is not 00:00:00 and this causes that in the WHERE condition, log_date BETWEEN CAST('03/13/2015' AS datetime) AND CAST('03/23/2015' AS datetime) leads to ie 2015-03-23 8:00:05 between ...


1

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point: ...


-1

SELECT GROUP_ID, Group_Name, SUM(Price) FROM ProductsTable GROUP BY GROUP_ID, Group_Name


0

a SUM is an aggregation of data, when working with aggregations you can specify the clause GROUP BY to show results grouped by what you need. SELECT Group_Name, SUM(Price) FROM Table GROUP BY Group_Name


0

INDEX(type, stamp) should speed up "Select 1" significantly, even without LOCK IN SHARE MODE. (Please provide SHOW CREATE TABLE for further discussion.) Edit Even though you have type and stamp in indexes, having these two fields in this order will be optimal for "Select 1": INDEX(type, stamp). This is because the optimizer can drill into the BTree to ...



Top 50 recent answers are included