SQL語法如下，col_A跟col_B在同一個table，我想在下面結果再加一欄全部的col_B加總合計該怎麼寫？
select distinct(col_A) , sum(col_B)
from table
where col_B>0
group by col_A;
已邀請的邦友 {{ invite_list.length }}/5
再加一欄？所以這一欄裡面的數字會都一樣喔~
如果你意思是最後面再加一列，就是樓上大大回的。
select *,sum(col_B) over (PARTITION BY 1) total
from(
select
col_A,
sum(col_B) "col_B"
from table
where col_B>0
group by col_A
) t;
然後，你已經下group by，前面就不要distinct了。
2019/3/14補充：
是喔~ 沒解出來嗎？ 那我改一下我寫的。
若要再加一列，那列叫做total的話，如下：
select
col_A,
sum(col_B) "col_B"
from table
where col_B>0
group by col_A
UNION ALL
select
'total' col_A,
sum(col_B) "col_B"
from table
where col_B>0
【再加一列於最底下】和【再加一欄於最右邊】的差異~
更新
select col_A, sum(col_B) as col_B
from tableX
where col_B>0
group by col_A
--
union all
select 'subtotal : ' + substring(col_A,1,1) as col_A, sum(col_B) as col_B
from tableX
where col_B>0
group by substring(col_A,1,1)
--
union all
select 'total' as col_A, sum(col_B) as col_B
from tableX
where col_B>0
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=48e7fe59daa58c5ade98dc7284a20949
create table ithelp190314 (
id smallint generated always as identity
, cola text not null
, colb smallint not null
);
insert into ithelp190314 (cola, colb) values
('a', 1), ('a', 2), ('a', 3),('a', -1),
('b', 2), ('b', 4), ('b', 6),('b', -2);
--
select cola
, sum(colb)
from ithelp190314
where colb > 0
group by cola;
+------+-----+
| cola | sum |
+------+-----+
| a | 6 |
| b | 12 |
+------+-----+
(2 rows)
--
select cola
, sum(colb) as "sum(colb > 0)"
, NULL::bigint as "all sum(colb)"
, NULL::bigint as "all sum(abs(colb))"
from ithelp190314
where colb > 0
group by cola
union all
select '----', NULL, NULL, NULL
union all
select 'All '
, NULL
, sum(colb)
, sum(abs(colb))
from ithelp190314
;
+------+---------------+---------------+--------------------+
| cola | sum(colb > 0) | all sum(colb) | all sum(abs(colb)) |
+------+---------------+---------------+--------------------+
| a | 6 | | |
| b | 12 | | |
| ---- | | | |
| All | | 15 | 21 |
+------+---------------+---------------+--------------------+
(4 rows)
題目又沒有要 abs, 為什麼要送 abs
節能減碳愛地球.
-- 另外一招
select cola
, sum(colb) as "sum(colb)"
, sum(abs(colb)) as "sum(abs(colb))"
from ithelp190314
group by rollup (cola)
order by cola NULLS LAST;
+------+-----------+----------------+
| cola | sum(colb) | sum(abs(colb)) |
+------+-----------+----------------+
| a | 5 | 7 |
| b | 10 | 14 |
| | 15 | 21 |
+------+-----------+----------------+
(3 rows)
select *
from ((
select col_A, sum(col_B)
from table
where col_B > 0
group by col_A
)union all(
select null, sum(col_B)
from table
where col_B > 0
)) as k
題目更新
select *
from ((
select substr(col_A,1,1) col_A
,sum(col_B) col_B
from tableX
where col_B > 0
and col_A <> ' '
group by substr(col_A,1,1)
)union all(
select null
,Sum(col_B)
from (
select substr(col_A,1,1) col_A
,sum(col_B) col_B
from tableX
where col_B > 0
and col_A <> ' '
group by substr(col_A,1,1)
) as k
)) as k
iThome鐵人賽
看影片追技術