Re: Where Not Exists with a twist.

[Felix Geerinckx]

David Powell wrote:

> I must sum the values for each location, but only if I have as many types as the maximum number
> of types.

On MySQL 4.1.x:

SELECT Location, SUM(Value)
FROM `Table`
GROUP BY Location
HAVING COUNT(DISTINCT type) = (SELECT COUNT(DISTINCT type) FROM `Table`);

On MySQL < 4.1 you cannot have subqueries and you will have to live with an extra column in your result:

SET @a := NULL;
SELECT @a := COUNT(DISTINCT type) FROM `Table`;
SELECT COUNT(DISTINCT type) AS t, Location, SUM(Value)
FROM `Table`
GROUP BY Location
HAVING t = @a;

--
felix
Please use BBCode to format your messages in this forum.