MySQLで特定のIPアドレス含むかのフラグと集約を同時にしたい

Question

前提・実現したいこと

MySQLで特定のIPアドレス含むかどうかのフラグを含めたSELECT文を作成したい

fooテーブル

id	foo_id	ip
1	1	aaa
2	1	bbb
3	1	ccc
4	2	bbb
5	2	ccc
6	2	ddd
7	3	aaa
8	4	bbb

欲しい結果(ipアドレスがaaaの人が実行するてい)

id	foo_id	count	flag
1	1	3	1
2	2	3	0
3	3	1	1
4	4	1	0

試したこと

元のテーブルにflagを追加

CASE WHEN ip = 'aaa' THEN 1 ELSE 0 END as flag

foo_idとipでユニークなのでSUMでflagを取得

SELECT id, foo_id, COUNT(*) AS count, SUM(flag) AS flag

これを組み合わせれば実現できそうですが、この先どうしていいかわからないので教えていただきたいです

補足情報（FW/ツールのバージョンなど）

MySQL 5.6

CREATE TABLE `default`.`foo` (
  `id` INT NOT NULL AUTO_INCREMENT , 
  `foo_id` INT NOT NULL , 
  `ip` VARCHAR(255) NOT NULL , 
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

INSERT INTO `foo` (`id`, `foo_id`, `ip`) 
VALUES ('1', '1', 'aaa'), ('2', '1', 'bbb'), ('3', '1', 'ccc'), ('4', '2', 'bbb'), ('5', '2', 'ccc'), ('6', '2', 'ddd'), ('7', '3', 'aaa'), ('8', '4', 'bbb')

Comments

[m.ts10806]

MySQLのバージョンは何でしょうか。
あと、テーブル定義（CREATE TABLE）とサンプルデータ（INSERT）を提示してください。

[eryngii]

本文に書いてしまったのでこちらにも転記いたします

バージョン MySQL 5.6

テーブル定義
CREATE TABLE `default`.`foo` (
`id` INT NOT NULL AUTO_INCREMENT ,
`foo_id` INT NOT NULL ,
`ip` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

サンプルデータ
INSERT INTO `foo` (`id`, `foo_id`, `ip`)
VALUES ('1', '1', 'aaa'), ('2', '1', 'bbb'), ('3', '1', 'ccc'), ('4', '2', 'bbb'), ('5', '2', 'ccc'), ('6', '2', 'ddd'), ('7', '3', 'aaa'), ('8', '4', 'bbb')

Answer 1

ご回答いただいた内容をヒントに解決できました！

select id, ip_list.foo_id, cnt, flag
from foo
inner join ( select foo_id , count(*) AS cnt , sum(case when ip='aaa' then 1 else 0 end) AS flag from foo group by foo_id ) ip_list on foo.id=ip_list.foo_id

Answer 2

こんな感じ。※欲しい結果のfoo_idについてはルールが分からないので無視しています。

select id, foo_id, cnt, flag
from  foo inner join (
        select ip
             , count(*) AS cnt
             , sum(case when ip='aaa' then 1 else 0 end) AS flag
        from foo
        group by ip
      ) ip_list
      on  foo.ip=ip_list.ip

Comments

[eryngii]

欲しい結果になりませんでしたが下記SQLで解決できました
ご回答のSQLが参考になりました！

select id, ip_list.foo_id, cnt, flag
from foo
inner join ( select foo_id , count(*) AS cnt , sum(case when ip='aaa' then 1 else 0 end) AS flag from foo group by foo_id ) ip_list on foo.id=ip_list.foo_id