#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    9

    Select from two quereis


    So I have two queries. The first one is this:

    Code:
    SELECT SUM(qq.ScrapPerContainer) as FinalSum FROM (
    SELECT q.container, MAX(q.qty) AS ScrapPerContainer FROM (
    SELECT t.containername, s.yieldcode, s.qty, s.container FROM (
    SELECT containername FROM eqdb_insite.dbo.dc_mz00300r
    WHERE txndate>='10/2/2011 12:00 AM' AND txndate<'10/9/2011 12:00 AM'
    ) as t
    LEFT JOIN (
    SELECT container, yieldcode, w.waferid, qty FROM eqdb_insite.dbo.dailyop_pareto
    LEFT JOIN eqdb_insite.dbo.waferid w ON container = w.wafergroupid
    WHERE operation IN ('MZ-00300', 'MZ-00302', 'MZ-00310', 'MZ-00320')
    ) AS s ON t.containername = s.container OR t.containername = s.waferid
    ) as q
    GROUP BY q.container
    ) AS qq
    and the second one is this:
    Code:
    SELECT COUNT(qqq.containername) as FinalCount FROM (
    SELECT containername FROM eqdb_insite.dbo.dc_mz00300r WHERE txndate>='10/2/2011 12:00 AM' AND txndate<'10/9/2011 12:00 AM') as qqq



    What I am trying to do now is get both of those select values in one query so my final goal will be:

    Code:
    SELECT blah.FinalCount - blah.FinalSum FROM ( ............ )
    In the ....... i need to place both of those queries in such a way that i get back FinalCount and FinalSum of the independent queries.

    Please help.
    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Code:
    SELECT humpty.FinalSum
         , dumpty.FinalCount
      FROM ( SELECT SUM(qq.ScrapPerContainer) as FinalSum 
               FROM ( SELECT q.container
                           , MAX(q.qty) AS ScrapPerContainer 
                        FROM ( SELECT t.containername
                                    , s.yieldcode
                                    , s.qty
                                    , s.container 
                                 FROM ( SELECT containername 
                                          FROM eqdb_insite.dbo.dc_mz00300r
                                         WHERE txndate>= '10/2/2011 12:00 AM' 
                                           AND txndate < '10/9/2011 12:00 AM' ) as t
                               LEFT OUTER
                                 JOIN ( SELECT container
                                             , yieldcode
                                             , w.waferid
                                             , qty 
                                          FROM eqdb_insite.dbo.dailyop_pareto
                                        LEFT OUTER
                                          JOIN eqdb_insite.dbo.waferid w 
                                            ON container = w.wafergroupid
                                         WHERE operation IN ('MZ-00300', 'MZ-00302'
                                                           , 'MZ-00310', 'MZ-00320') ) AS s 
                                   ON t.containername = s.container 
                                   OR t.containername = s.waferid ) as q
                      GROUP 
                          BY q.container ) AS qq
           ) AS humpty
    CROSS
      JOIN ( SELECT COUNT(qqq.containername) as FinalCount 
               FROM ( SELECT containername 
                        FROM eqdb_insite.dbo.dc_mz00300r 
                       WHERE txndate >= '10/2/2011 12:00 AM' 
                         AND txndate  < '10/9/2011 12:00 AM') as qqq
           ) AS dumpty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    9
    Wow thanks so much 937!! I LOVE your choice of words :P what is that as parameter called in sql?

    Thanks again!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by pg300
    what is that as parameter called in sql?
    it's called a column alias or a table alias
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    9
    Thanks again!
    I didn't get to test this but I was just wondering will this work in sql server?

    The cross join function is not documented here:
    http://www.w3schools.com/sql/sql_join_left.asp

    Is there a place I can sql server documentation?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by pg300
    I was just wondering will this work in sql server?
    yes

    Originally Posted by pg300
    Is there a place I can sql server documentation?
    yes... google SQL Server Books Online

    that will lead you to da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    9
    ah i thought there were different versions of sql server. i am very new to all of this (just 2 weeks old) thanks so much again!

IMN logo majestic logo threadwatch logo seochat tools logo