Select within select?

Posted on 2011-10-23
Last Modified: 2012-05-12

Is it possible in SQL to create query that will use Select within select statement.

For example:

I have two tables in database. I need data SUM,IDN,DATE

In one table i have SUM and IDN, in another I have DATE and IDN.

Now i want to create query that will gather data SUM,IDN, and DATE and order it by DATE value.

IDN is of same value in both tables (always).

Question by:Grogel
    LVL 92

    Accepted Solution

    You can, but in this case there is no need:

    SELECT t2.[DATE], t2.IDN, SUM(t2.[SUM]) AS Qty
    FROM table1 t1 INNER JOIN
        table2 t2 ON t1.[DATE] = t2.[DATE] AND t1.IDN = t2.IDN
    GROUP BY t2.[DATE], t2.IDN
    ORDER BY t2.[DATE], t2.IDN

    Open in new window


    Author Comment

    Thank you, however i do have one problem with this solution.

    That data must not be summed. When i change SUM(t2.[SUM]) to t2.[SUM] i am getting following error

    Column ' t2.[SUM]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    SUM field is number which is added in database by user, Date is automatic datetime, IDN is somekind of uniqueidentifier.

    I need all SUM inputs (but not SUMMED) to be displayed along with Date.

    Sorry for my English, it's not my native language.

    Author Comment

    Never mind, sorry. I don't need Group by so everything works just fine. Sometimes my fingers are faster then my brain.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now