Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    7

    Unanswered: Subquery in the SELECT list

    My Uni assignment asks me to:

    "Write a query that has a subquery in its SELECT list." It then says "a subquery in a SELECT list usually makes more sense if it is correlated however subqueries don't have to be correlated if a fixed value is wanted. "

    I have written my first attempt at the query that I want for this question:

    Select count (*) As CountTable (
    Select CompetitorNum, FamilyName, Gender, CountryCode
    From Competitors
    Where Gender = 'Male')

    I have two questions:

    1. Can you please explain to me what it means about subqueries being correlated?

    2. The query I have written returns a table, with a count of 1 for the first half. The second half returns all the records which match the criteria. Why do I get the table of 1? How can I change this query so that it satisifies the criteria but doesn't just return a table of 1?

    I have attached the database script if necessary. I am using both SQL Server and Postgres, but I'm analysing these purely from a SQL viewpoint that's why I didn't post under those forums.

    Thanks!!

    Nerddette

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Correlated means that the subquery is restricted by a value from the main query. My understanding of "subquery in the select list" would be what Oracle (at least) calls a "scalar subquery". An example (correlated, too):
    Code:
    select dept.dname
    ,      (select count(*) from emp
            where  emp.deptno = dept.deptno /* This is the correlation */
           ) as headcount
    from   dept;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select dept.dname
         , ( select count(*) 
               from emp
              where emp.deptno 
                  = dept.deptno   /* correlated */
           ) as headcount
         , ( select count(*) 
               from emp
              where emp.deptno 
                  = dept.deptno ) * 100
         / ( select count(*) 
               from emp        /* not correlated */
            ) as deptpercent 
      from dept;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •