MySQLでのsumとそれ以外のカラムの取得

Question

###前提・実現したいこと
MySQLで詰まっています。
1つのSQLであるカラムの集計（該当する項目のみ）集計に関係のないカラムの値が取りたいです。可能であればSQL文を教えてください。

###発生している問題・エラーメッセージ

簡単にするために以下のようなテーブルを作ってみました。
＜testテーブル＞
id int ai
up_time datetime
qty int
sub_id int
picupno int

データ
id up_time qty sub_id picupno
1 2017-10-01 00:00:00 20 1 0
2 2017-09-30 07:00:00 30 1 0
3 2017-10-02 00:00:00 100 2 1
4 2017-10-03 07:00:00 200 2 2

読みにくくてすいません。
sub_idごとにqtyの合計が必要です。更にsub_idごとのup_timeの毎の一番新しいup_timeとpicupnoが欲しいのです。

望む結果は、
up_time picupno sum(qty)
2017-10-01 00:00:00 1 50
2017-10-03 07:00:00 2 300

up_timeの最新（sub_idごとの）でその行のpicupnoとsub_idごとのsum(qty)です。

###試したこと
SELECT *,sum(qty) FROM test WHERE sub_id IN(1,2) GROUP BY sub_id
や
SELECT *,max(up_time), sum(qty) FROM test GROUP BY sub_id order by up_time DESC

いづれもup_timeが直近ではなく、picupnoも望むものではありません。

よろしくお願いいたします

###補足情報(言語/FW/ツール等のバージョンなど)
実動環境では
SELECT *,sum(qty) … GROUP BY sub_id
とかやると非集約ラムは参照できないというようなエラーが発生します。

Answer 1

sub_id毎の最新日付のデータを取得することと、sub_id毎の集計を取得することを分けて取得することを思いつけば、わりとすんなりと書けると思います。

select up_time, picupno
     ,(select sum(qty) from test where sub_id=t1.sub_id) as sum_qty
from test as t1
where up_time=(select max(up_time) from test where sub_id=t1.sub_id)

一応別解（相関問い合わせによるサブクエリーを使用しない版）

select  t1.up_time, t1.picupno, t2.sum_qty
from    test as t1 
        inner join (
            select sub_id, max(up_time) as max_up_time, sum(qty) as sum_qty from test group by sub_id
        ) as t2
        on t1.sub_id=t2.sub_id and t1.up_time=t2.max_up_time

MySQLはサンプルデータを触っている程度なので、大量のデータでのSQLの記述による性能差などは見地がありません。
なので、結果をフィードバックして頂けると喜びます。

Comments

[inst_yama]

早々とご回答ありがとうございます。
1つ目のSQLは、自分の理解力的に最適でした。

inner joinを使うのだろうとは思っていましたが、自分自身のテーブルをうまく連結することでsub
_idカラムで出現するすべてのデータになることに思い至りませんでした。

実務では別解の方を利用するつもりです。何かフィードバックをできればと思います。
ありがとうございました。

Answer 2

更にsub_idごとのup_timeの毎の一番新しいup_timeとpicupno

この条件であれば、up_timeとpicupnoのMAXをとればよいのではないでしょうか。

SELECT MAX(up_time), MAX(picupno), SUM(qty) FROM test GROUP BY sub_id 

Comments

[inst_yama]

ご回答ありがとうございます。
希望する結果と同じものとなりました。

SQLがよくわかっていないので疑問が少し残りますが、常にup_timeと同じレコードのpicupnoが取得できるのでしょうか。質問自体がわかりにくい書き方だったかもしれません。

もし追加でご説明いただけるなら幸いです。

Answer 3

sub_idとup_timeの値が同じ行がないことを条件に、下記のように考えました。

SELECT t1.sub_id, t1.picupno, t2.qty, t2.up_time FROM test t1
INNER JOIN (SELECT sub_id, SUM(qty) qty, MAX(up_time) up_time FROM test GROUP BY sub_id) t2
ON t1.sub_id = t2.sub_id AND t1.up_time = t2.up_time;

Comments

[inst_yama]

ご回答ありがとうございます。

希望する結果が得られました。
このSQLをベースに実務に合わせていこうと思います。