CODEWITHSUNDEEP
mysqldatabase
onmylemon
onmylemon

Reputation: 734

Mysql aggregating data with if statement

Simplified example of a problem I am trying to solve:

EDIT - With PK

sqlfiddle: http://sqlfiddle.com/#!2/7be5d/1

---

I have two tables:

People
--------
| Name |
--------
| Mike |
| Jim  |
| Fred |
--------


HasBanana
------------------------
| Name  | Has_a_banana |
------------------------
| Mike  | N            |
| Mike  | N            |
| Mike  | N            |
| Mike  | N            |
| Mike  | Y            |
| Mike  | N            |
| Jim   | N            |
| Jim   | N            |
| Jim   | Y            |
| Jim   | N            |
| Jim   | N            |
| Fred  | N            |
| Fred  | N            |
| Fred  | N            |
| Fred  | N            |
| Fred  | N            |
| Fred  | N            |
------------------------

And a query:

select * from People
left join HasBanana on HasBanana.name = People.name;

This produces a table exactly the same as the HasBanana table above. What I would like to produce is a table grouped by People.name and showing Y if the person has ever been marked as having a banana.

So something like this:

HadBanana
------------------------
| Name  | Had_a_banana |
------------------------
| Mike  | Y            |
| Jim   | Y            |
| Fred  | N            |
------------------------

I know I can use the following to group by name

group by people.name;

but having issues doing the logic to say for each name if has_a_banana has ever been Y then set had_a_banana to Y.

Upvotes: 1

Views: 61

Answers (4)

Strawberry
Strawberry

Reputation: 33945

E.g.:

SELECT DISTINCT x.name
              , COALESCE(y.has_a_banana,x.has_a_banana) has_a_banana
           FROM bananas x
           LEFT 
           JOIN bananas y
             ON y.name = x.name
            AND y.has_a_banana = 'y'

http://sqlfiddle.com/#!2/7be5d/25

Upvotes: 0

Alfons
Alfons

Reputation: 531

Alternative (bit of an ugly solution): 

select people.name, max(has_a_banana) from people
left join bananas on bananas.name = people.name
group by people.name;

Upvotes: 1

Eric Yang
Eric Yang

Reputation: 2750

select people.id, people.name, case a.has_a_banana when 'Y' then 'Y' else 'N' end as has_a_banana from people
left join 
(select people.id, people.name, bananas.has_a_banana from people
left join bananas on bananas.name = people.name
where has_a_banana = 'Y'
group by people.name) as a 
on a.name = people.name

Should allow you to get what you want.

Upvotes: 0

davek
davek

Reputation: 22925

select 
   p.name
   , case when x.name is null then 'N' else 'Y' end as had_a_banana
from people p
left outer join
(
  select 
     name 
  from bananas
  where has_a_banana = 'Y'
  group by name
) x
on x.name = p.name
group by p.name, x.name

I've checked this using your SQLFiddle link, although your Fiddle data does not match your question, so I get:

Fred  N
Jim   Y
Mike  N

(which seems to match your Fiddle data).

Upvotes: 1

Related Questions