Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: subtracting 2 columns, with the group by clause

    Hi,
    I have two select statements

    Stmt1:

    Code:
    Select count(*) count, fruit from table1 group by fruit
    
    Result:
    
    count-----fruit
    
    12--------Apple
    34--------Orange
    24--------Melon
    Stmt2:

    Code:
    Select count(*) count, fruit from table2 group by fruit
    
    Result:
    
    count-----fruit
    
    10--------Apple
    30--------Orange
    20--------Melon
    Can some one please help me with a select statement which gives me the difference between the above two results, with the group by clause intact

    Result::

    Code:
    count-----fruit
    
    2--------Apple
    4--------Orange
    4--------Melon

    thanks in advace!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    SELECT t1.count - t2.count, t1.fruit
    FROM ( <query1> ) AS t1 JOIN ( <query2> ) AS t2 ON ( t1.fruit = t2.fruit )
    Just remember: pretty much everything in SQL is a table. A query produces a table as output, and you can use a table in the FROM clause of a query, for example. Just combine these two things and you have subselects.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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