group by のソート

質問

mysql Client API version 3.23.49
PHP/4.4.5
で動作させようと思っています。

table
a　　　b
1　　　2
3　　　4
2　　　4
7　　　1
2　　　6
1　　　6

上のデータから以下のような結果を得たいのですが
table
a　　　b　　idcount1
1　　　6　　　　2
3　　　4　　　　1
2　　　6　　　　2
7　　　1　　　　1

グループで集計をとり、その上でbに6がある場合は6その他は
bの値にNULLもしくは別の値が入るという形でいいのですが・・・
できません。

SELECT a, b, count( * ) AS idcount1
FROM table
GROUP BY a
するとはじめに読みこんだ値がbには入ってしまいます。
結果
table
a　　　b　　idcount1
1　　　2　　　　2
3　　　4　　　　1
2　　　4　　　　2
7　　　1　　　　1

SELECT a, b, count( * ) AS idcount1
FROM table
where b=6 GROUP BY a
するとbの値ははじきますし、カウントしている意味がなくなります。
結果
table
a　　　b　　idcount1
1　　　6　　　　1
2　　　6　　　　1

GROUP BY 処理前のソートを考え、from table後に oder by b=6 asc を使ったがエラー
末尾に oder by b=6 asc を使うと最初に試した値をソートするだけでした。
宜しくお願いいたします。

回答No.2

agricap · ベストアンサー

ややこしいので、tableじゃなくて、tと書きます。

SELECT a, MAX(IF(b=6,6,0)) as b1, count(*) as idcount1
FROM t GROUP BY a

でどうでしょう？

条件がよくわかりませんが、bは6が最大値なら、もっと簡単に

SELECT a, MAX(b) as b1, count(*) as idcount1
FROM t GROUP BY a

お礼

agricap 先日はお世話になりました。
前回の分に新たな命令文を組み込もうとして行き詰まったしだいです。
完璧ですね！

ひとつ疑問が。SQLポケットリファレンスを見ながらやっています。
私もsqlにphpやc言語のようなif分があれば簡単かつシンプルに解決できると思い調べたんですが、ifは
プロシージャ内で使用できる命令
oracle,sqlsever,db2,postgresqlでしか使えないと書いてありました。
なぜ！？

補足

下の件
ネットでフロー制御関数というのを見つけました。
本に書いていなかったということで解決です！

回答No.1

yambejp

特定キーでgroup by した場合は、集計関数をつかわない別キーの値は
保障されません。（自信ないけどそんな感じだったはず）
3.23系ですよね？一度テンポラリにおとすといいのではないでしょうか？
テーブル名にtableという予約語をつかうとエラーになりますので
運用には注意が必要です。

CREATE TEMPORARY TABLE t2
SELECT DISTINCT a,b
FROM `table`
WHERE b=6;
SELECT t1.a,t2.b, count( * ) AS idcount1
FROM `table` as t1
LEFT JOIN t2 ON t1.a=t2.a
GROUP BY a

お礼

特定キーでgroup by した場合は、集計関数をつかわない別キーの値は
保障されません。
そうですね。調べたら出来そうになかったので質問したのですが、
yambejpの回答で解決しました。
ありがとうございます。

sqlはデータベースを動かす為だけなのでできる事がシンプルですね。
こみいった事をしようとすると命令文が複雑になってしまう・・・

慣れだとは思いますが、苦手だ！