SQLiteでunionやgroup byを使って小計の入ったデータに整形したい

Question

いつもありがとうございます。
ちょっとタイトルの意味がわかりづらくて恐縮ですが今回、sqliteでデータの小計を入れるというのをやっています。

データベースの設計は以下のとおりで、この例では最上位項目にカテゴリーとして、和食、洋食の2種類あり、さらに各々の下に具体的な料理名がぶら下がっており、各料理名は価格情報を持っています。そしてその価格の小計をやったうえで再度同じ順番で結果を出したいと思っております。

なにかいい方法、考え方などがあればご教示下さいますよう、お願い致します。

環境
sqlite3.8.5

CREATE TABLE test (id integer primary key, name text, cat integer, menu integer, price integer);

適当にデータを入れた結果

id    name        cat   menu  pric
----  -------------    ----  ----  ----
10    和食           1     0     0   
11    そば           1     1     100 
12    うどん         1     2     100 
20    洋食           2     0     0   
21    オムライス     2     1     100 
22    ハンバーグ     2     2     100 

惜しいが失敗した例
和食、洋食の小計を入れつつ、順番も保持する
select (id/10)*10 as id, (select name from test where id=(id/10)*10) as name, cat, 0 as menu, sum(price) as price from test group by cat union all select id, name, cat, menu, price from test where menu!=0 order by id;

id    name        cat   menu  pric
----  -------------    ----   ----     ----
10    和食           1     0     200 
11    そば           1     1     100 
12    うどん         1     2     100 
20    和食           2     0     200  ←洋食になっていないため失敗
21    オムライス     2     1     100 
22    ハンバーグ     2     2     100 

失敗例

select id, name, cat, menu, sum(price) as price from test group by cat union all select id, name, cat, menu, price from test where menu!=0 order by id;
id    name           cat   menu  pric
----  -------------    ----    ----  ----
11    そば          1     1     100 
12    うどん        1     2     200 
12    うどん        1     2     100 
21    オムライス    2     1     100 
22    ハンバーグ    2     2     200 
22    ハンバーグ    2     2     100 

単純なgroup by とunionの組み合わせだと順番が不満でかつ合計された行の名前が和食、洋食にならない（group by がその最終行を見ているため？）。

Answer 1

menuが0の場合のみpriceの計算を行う方法はどうでしょう。

select
  id
  , name
  , cat
  , menu
  , case 
    when menu = 0 
    then ( 
      select
        sum(price) 
      from
        test tt 
      where
        tm.cat = tt.cat
    ) 
    else price 
    end price
from
  test tm;

Comments

[true]

素晴らしすぎます！実は同じ考え方でcase式ぶち込んでやってたんですけどそのなかでエイリアス名つけてやるところまで思い至りませんでした。。case式の理解が3歩くらい進んだ気持ちになりました。ありがとうございました！

[true]

しかもunionもgroup byも要らないですね。恐れ入りました。。。

Answer 2

結局、集計したい行のidを大きくなるようにして、menuを0のままにして、表示するときにorder by menuとすることで無理やり実現させました。

和食のid 199 洋食 299 としたうえで、
select id, name, cat, menu, sum(price) as price from test group by cat union all selecct id, name, cat, menu, price form test where menu!=0 order by cat, menu;

あまり根本的な解決にはなっていませんがとりあえずこれでいっときます。