Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    1

    Unanswered: Two tables group and Subtract results?

    I have two tables I need to group them, count them then subtract the result. I have no clue what the query will look like. I think I may need a join? but I am not sure. Here is what my data looks like.



    Table 1:

    id | user_id | item_id | item_name
    ===============================
    1 | 2 | 1 | apple
    2 | 2 | 1 | apple
    3 | 2 | 2 | orange
    4 | 2 | 3 | banana
    5 | 2 | 3 | banana

    When grouped and counted is:

    apple 2
    orange 1
    banana 2

    Table 2: (needs to be subtracted from table 1)

    id | user_id | item_id | item_name
    ===============================
    1 | 2 | 1 | apple
    3 | 2 | 2 | orange
    4 | 2 | 3 | banana

    When grouped and counted is:

    apple 1
    orange 1
    banana 1

    The information I am trying to get should be:

    item_name | amount
    ===============================
    apple | 1
    orange | 0
    banana | 1

    This is the function I have so far to count and group one table:

    PHP Code:
    function doReadItem($id) {
            
    // Create SQL query
            
    $sql sprintf("SELECT *, count(item_name) as amount 
            FROM  user_items
            WHERE user_id = 
    $id
            GROUP BY item_name"
    );
            
    // Run query on database
            
    $result mysql_query($sql);
            
    // Return result
            
    return $result;
        } 
    Last edited by Oceankoi; 02-09-07 at 00:07.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select item_name, sum(subtotal) as amount
      from (
           select item_name, count(*) as subtotal 
             from table1
           group by item_name  
           union all
           select item_name, -count(*) 
             from table2
           group by item_name  
           ) as dt 
    group by item_name
    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
  •