Добавьте строку с итоговыми значениями для каждого столбца

Я пытаюсь добавить строку в запрос выбора SQL, который содержит сумму (или любую другую операцию, например, среднее значение, если на то пошло) в качестве последней строки. В этом он чем-то похож на GROUP BY... WITH ROLLUP.

Для начала: допустим, у меня есть таблица t с полями ID, A, B и C, все из которых являются числами. Кроме того, идентификатор не уникален, а скорее является категорией. Мой запрос SELECT должен подсчитывать, сколько из этих чисел попадают в указанный диапазон.

Так, например, конечным результатом будет

(SELECT t.ID, a.ac, b.bc, c.cc FROM t
LEFT JOIN (SELECT COUNT(*) cc,ID FROM t WHERE A BETWEEN 2 AND 4 GROUP BY ID) AS a ON a.ID=t.ID 
LEFT JOIN (SELECT AVG(B) cc,ID FROM t WHERE B BETWEEN 19 AND 40 GROUP BY ID) AS b ON b.ID=t.ID 
LEFT JOIN (SELECT COUNT(*) cc,ID FROM t WHERE C BETWEEN 12 AND 14 GROUP BY ID) AS c ON a.ID=t.ID GROUP BY t.ID) 

union 

(select 'Overall',
 (SELECT COUNT(*) cc FROM t WHERE A BETWEEN 2 AND 4),
 (SELECT AVG(B) cc FROM t WHERE B BETWEEN 19 AND 40),
 (SELECT COUNT(*) cc FROM t WHERE C BETWEEN 12 AND 14) );

Однако это решение не идеально, так как мне нужно заново сформулировать условия для A, B и C. Я хотел бы знать, есть ли простой способ добиться того же результата, указав условия только один раз.

Заранее спасибо.


mysql sql rollup union
person corrideat    schedule 12.12.2012    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Я не думаю, что есть более простое решение. Но я бы переписал ваши запросы так:

SELECT
  t.ID,
  count(case when A between 2 and 4 then ID end),
  AVG(case when B between 19 and 40 then B end),
  COUNT(case when C between 12 and 14 then id end)
FROM t
GROUP BY ID
UNION
select
  'Overall',
  count(case when A between 2 and 4 then ID end),
  AVG(case when B between 19 and 40 then B end),
  COUNT(case when C between 12 and 14 then id end)
FROM t
person fthiella    schedule 12.12.2012
comment
+1, вы можете сделать то же самое во втором общем запросе, и вот демонстрация если вы хотите его использовать. - person Mahmoud Gamal; 13.12.2012
comment
@MahmoudGamal спасибо :) я редактировал до того, как увидел ваш комментарий, он не идентичен вашей демонстрации :) - person fthiella; 13.12.2012

arrow_upward
2
arrow_downward

Ну и без среднего можно обойтись:

SELECT t.ID,
       count(case when A between 2 and 4 then ID end),
       AVG(case when B between 19 and 40 then B end),
       COUNT(case when C between 12 and 14 then id end)
FROM t
GROUP BY ID with rollup

Что я бы написал как:

SELECT t.ID,
       sum(case when A between 2 and 4 then 1 else 0 end),
       sum(case when B between 19 and 40 then B end)/sum((case when B between 19 and 40 then B end),
       sum(case when C between 12 and 14 then 1 else end)
FROM t
GROUP BY ID with rollup

Я не уверен, как свертка работает со avg, поэтому окончательная версия:

select coalesce(t.ID, 'Overall'), Acnt,
       (case when Bcnt > 0 then Bsum/Bcnt end),
       Ccnt
from (select t.ID,
             sum(case when A between 2 and 4 then 1 else 0 end) as Acnt,
             sum(case when B between 19 and 40 then B end) as Bsum,
             sum(case when B between 19 and 40 then B end) as Bcnt,
             sum(case when C between 12 and 14 then 1 else end) as Ccnt
      FROM t
      GROUP BY ID with rollup
     ) t
person Gordon Linoff    schedule 13.12.2012