SQL Help

[TimDarracott]

This must be easy but i can't get my head around it.

This is a very simplified version of a query i'm working on:

select department, product count(*)
from table
where department in ('55', '60')
group by department, product

That query in it's simplest form will return a few rows, a count for every product that has a record in 55 and/or 60.

Now in the same query i want a column which shows the answer to the count of 55 subtracted from the count of 60 for each product.

Any ideas?

I'm at a loss, i've got around it by creating two temp tables and joining them together but i'm sure there are better ways of doing it!

[Don]

It isn't easy at all!

You could do it with subqueries though....

SELECT
PRODUCT AS PRODUCT,
(SELECT COUNT(*) FROM TABLE WHERE TABLE.PRODUCT = PRODUCTS.PRODUCT AND DEPARTMENT = '55') AS IN55,
(SELECT COUNT(*) FROM TABLE WHERE TABLE.PRODUCT = PRODUCTS.PRODUCT AND DEPARTMENT = '60') AS IN60,
((SELECT COUNT(*) FROM TABLE WHERE TABLE.PRODUCT = PRODUCTS.PRODUCT AND DEPARTMENT = '55') -
(SELECT COUNT(*) FROM TABLE WHERE TABLE.PRODUCT = PRODUCTS.PRODUCT AND DEPARTMENT = '60')) AS THEDIFFERENCE
FROM PRODUCTS

This sort of thing might take you where you want to go...

[TimDarracott]

Thanks alot

The two temp tables don't seem to be affecting performance, i might stick with that.

I'll give the sub queries a go tomorrow, thanks again!