Two tables group and Subtract results?

Oceankoi · #1 (**permalink**) 02-08-07, 20:31

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;
    }

r937 · #2 (**permalink**) 02-09-07, 04:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech