SELECT      BRANCH_ADD,     COUNT(TRANS_AMOUNT) AS TRANS  FROM      (SELECT           A.BRANCH_ADD, C.TRANS_AMOUNT      FROM           BRANCHES A, ACCTS B, TRANSACTION C      WHERE           a.branch_code = b.branch_code          AND b.acct_no = c.acct_no      ORDER BY           BRANCH_ADD) GROUP BY      BRANCH_ADD;   Output of the above query :    as text :  BRANCH_ADD    TRANS ------------  ------ TOWN_HALL     2 CHAMRAJPET    4 CITY_MARKET   4
13936
Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select *  from sometable order by name limit 20,10  to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically. In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this: select *  from sometable where rownum &lt;= 10 order by name  will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.
28370
I have a table similar to this:  CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer);   I can use the parentid field to arrange data into a tree structure.  Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?  UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.  BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
31050
I need to run a query like:  SELECT p.id, p.name,         (SELECT name            FROM sites s           WHERE s.id = p.site_id) AS site_list   FROM publications p   But I'd like the sub-select to return a comma separated list, instead of a column of data.  Is this even possible, and if so, how?
72038
I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3, the number of scores between 4 and 6, and the number of scores between 7 and 10. Something like the following:  SELECT   COUNT(*) as total,   COUNT(     SELECT * from foo where score between 0 and 3;   ) as low,   COUNT(     SELECT * from foo where score between 4 and 6;   ) as mid,   COUNT(     SELECT * from foo where score between 7 and 10;   ) as high FROM foo;   I tried this, but got an error with the SELECT in the COUNT statements. Any ideas how I can do this? I'm sure there's a super simple way in Postgres. I just can't figure out the correct terms to Google for.
35819
I learned something simple about SQL the other day:  SELECT c FROM myTbl GROUP BY C   Has the same result as:  SELECT DISTINCT C FROM myTbl   What I am curious of, is there anything different in the way an SQL engine processes the command, or are they truly the same thing?    I personally prefer the distinct syntax, but I am sure it's more out of habit than anything else.  EDIT: This is not a question about aggregates. The use of GROUP BY with aggregate functions is understood.
12650
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.  What are the differences between these two statements, if any?
98617
I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.  How things start:  SELECT COUNT(fullpath) FROM data;  12395   I use the column fullpath a lot so I create an index:  CREATE INDEX "index" ON data (fullpath);   I then add the column:  ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);   Insert values into the Sub column based on the fullpath:  insert into data(Sub) select case     when fullpath like '%subA%' then 'subA'     when fullpath like '%subB%' then 'subB'     else 'neither' end from data;   Check the number of rows again:  SELECT COUNT(fullpath) FROM data;  24790   I don't understand the syntax and the operations. Do I need to join or something to respect the index?
81760
Do any queries exist that require RIGHT JOIN, or can they always be re-written with LEFT JOIN?  And more specifically, how do you re-write this one without the right join (and I guess implicitly without any subqueries or other fanciness):   SELECT * FROM t1 LEFT JOIN t2 ON t1.k2 = t2.k2 RIGHT JOIN t3 ON t3.k3 = t2.k3
46559
One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?
106834
\textbf{47.Consider the following relations:\\ \includegraphics[width=120mm]{Image6.png}\\ Consider the following SQL query.\\  SELECT S.Student$_$Name, sum (P.Marks)\\  FROM Student S,Performance P\\ WHERE S.Roll$_$No=P.Roll$_$No \\ GROUP BY S.Student$_$Name\\  The number of rows that will be returned by the SQL query is .........}\\ \vspace{2mm}\\   Error Missing { inserted.... Please help me out!
61514
(Follow up from this post: )    PostgreSQL 9.5.  I can't divulge full details, but table has 22 columns and 5 indexes:   primary key ('pk'), text (btree) another text (btree) a timestamp with time zone (btree) a tsvector (gin) my latest one, a bigint (btree)   (From the previous post you know that I was trying to avoid creating this additional column, and just use an expression index--adding two integer columns together--without success.  The bigint column here probably could have been just 'integer' but I made a mistake creating it; it took about an hour to add the column, populate it, and reindex, so I'm hoping this isn't relevant but mentioning it just in case it is.)  All are btree except the tsvector.  The following queries all take only 12ms and use just an Index Scan:   SELECT pk FROM table ORDER BY pk DESC LIMIT 10 SELECT pk FROM table ORDER BY text_column DESC LIMIT 10 SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10   But if I try to use my new bigint index for ORDER BY:  SELECT pk FROM table ORDER BY bigint_column DESC LIMIT 10  ...it takes 2.7s and uses Limit -&gt; Sort -&gt; Seq Scan.  My "cheating" method is the closest I seem to be able to get to making use of the index:  SELECT pk FROM table WHERE bigint_column &gt; 1000000 ORDER BY bigint_column DESC LIMIT 10   This takes 12ms and uses Limit -&gt; Sort -&gt; Bitmap Heap Scan (bigint_column &gt; 1000000) -&gt; Bitmap Index Scan (bigint_column &gt; 1000000).  This is after a VACUUM ANALYZE after adding the index.  I thought it strange that my expression index was not being used in the other question.  Now it's just a plain old column (I haven't even added what would be the necessary triggers to actually go this route.)  Why isn't my newest index being used, when the other three work "just fine"?  (As pointed out in the comments at  , an Index-Only Scan would be even better.  I fail to see why all of these queries wouldn't use at least an Index Scan, let alone an Index-Only Scan, instead of a full Seq Scan.)  The index definition has DESC NULLS LAST (although it's a non-nullable column.)
106704
Which is the valid syntax of this query in MySQL?  SELECT * FROM courses WHERE (now() + 2 hours) &gt; start_time   note: start_time is a field of courses table
15700
Basically the question is how to get from this:   foo_id   foo_name 1        A 1        B 2        C   to this:   foo_id   foo_name 1        A B 2        C
113361
I'm using Oracle database and I've created a test table "Table1" with a varchar column "column1". I ran the following loop to add some rows:  BEGIN     FOR i in 1..10 LOOP         INSERT INTO TABLE1 (COLUMN1)          VALUES (i);     END LOOP; END;   If I run 'select count(*) from table1' I get '10' as my result. However, in SQL Developer, nothing shows up. I can add rows manually and commit them to the database(these appear in SQL Developer), but I can't seem to view the ones created in sqlplus command line. Why is that?
56409
I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?  EmpID         Salary 1             90000 2             80000 3             54000 4             37000 5             12000 6             69000 7             50000  SELECT * FROM Employee E1 WHERE (N-1) = (                 SELECT COUNT(DISTINCT(E2.Salary))                 FROM Employee E2                 WHERE E2.Salary &gt; E1.Salary               )   If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!
77514
I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs.  select  c.id, c.base32_id, c.author, c.message, c.title, c.type, c.datetime, (select count(*) from comment where parent_id= c.id ) as ChildCount from comment c where c.parent_id is null order by id asc limit 25
33958
I have a table that looks like this (oversimplified case of the actual case to make my point)  CREATE TABLE sales (    id int,    currency VARCHAR(5),    price int    shop_id int );   Now I need to make a query that will give me something like this table, grouping by shops yet giving sum of sales per currency My problem is when I make currency column non aggregate it fails because my query becomes non standard. When I add currency to group by then each currency creates new row, something I don't want. I have run out of option and would appreciate any help or pointer to the right direction.  I use MySQL but would appreciate if I would get the solution that works with more databases (PostGreSQL being at the top of the list after MySQL)  Query that works with undesired results  SELECT   shop_id,           (CASE WHEN currency= "GBP" THEN SUM(price) ELSE 0 END) AS POUND,           (CASE WHEN currency= "USD" THEN SUM(price) ELSE 0 END) AS USDOLLAR GROUP BY shop_id, currency   The data that should be produced by the query  shop_id     |   POUND   |   USDOLLAR     -----------------------------------------     1       |   400     |   300 -----------------------------------------     2       |   250     |   100 -----------------------------------------     3       |   400     |   100 -----------------------------------------