MySQL Forums
Forum List  »  InnoDB

Where Not Exists with a twist.
Posted by: David Powell
Date: June 15, 2005 08:45AM

A little help here please.

Location type Value
23 A 12.5
23 B 13.6
23 C 14.7
34 A 15.9
34 B 16.0
34 C 17.2
45 A 18.3
45 B 19.4

I must sum the values for each location, but only if I have as many types as the maximum number of types. So in this example, I have types A,B, and C so I only want to work on the two locations that have A,B, and C types. If a new row with a 4th type were added to the data, I'd only want to sum those rows with types A,B,C, and D.

I understand group by location.
I understand select sum(value)
I'll use select distinct(type) to come up with a complete list of individual types.

In this example I need to exclude location 45 data because it lacks data of type C.

I'm looking for something that does
Where there is location data for each of the types found in (select distinct(type)

How do I do that?

TIA, Dave

Options: ReplyQuote

Written By
Where Not Exists with a twist.
June 15, 2005 08:45AM

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.