MySQL Forums
Forum List  »  InnoDB

Re: Where Not Exists with a twist.
Posted by: Felix Geerinckx
Date: June 17, 2005 07:49AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2920
June 15, 2005 08:45AM
Re: Where Not Exists with a twist.
1973
June 17, 2005 07:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.