iT邦幫忙

0

SQL 問題 distinct 後加總後再加入總計

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;

看更多先前的討論...收起先前的討論...
可以出個一些資料表的資料~表示你的方式~跟想要的答案嗎?
tlcric iT邦新手 5 級 ‧ 2019-03-14 09:45:30 檢舉
我實際上要撈的資料如下
select substr(col_A,1,1) , sum(col_B) from tableX
where col_B > 0
and col_A <> ' '
group by substr(col_A,1,1)
/*撈欄位A(文字)取最左邊一碼,統計相同碼的加總
tableX
col_A col_B
A123 2
A124 5
B123 6
B124 8
結果
A 7
B 14
我想在後面再加一個總計
一樣是union all顯示即可~
tlcric iT邦新手 5 級 ‧ 2019-03-14 10:55:16 檢舉
用union all就可以得到我要的結果了,謝謝大大
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
張小馬~
iT邦新手 3 級 ‧ 2019-03-13 18:06:52
最佳解答

再加一欄?所以這一欄裡面的數字會都一樣喔~
如果你意思是最後面再加一列,就是樓上大大回的。

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

再加一列於最底下】和【再加一欄於最右邊】的差異~
https://ithelp.ithome.com.tw/upload/images/20190314/20111566xYFxcpQNmK.png

tlcric iT邦新手 5 級 ‧ 2019-03-14 09:36:32 檢舉

原來下了group by就不需要distinct了,謝謝大大賜教,真的學到了

tlcric iT邦新手 5 級 ‧ 2019-03-14 09:38:33 檢舉

我題目打錯了,是再加一「列」加總,我照樓上大大的方式還沒解出來,再試看看。

了解,已更新回答~/images/emoticon/emoticon33.gif

1
rogeryao
iT邦大師 1 級 ‧ 2019-03-13 17:14:51

更新

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

1
一級屠豬士
iT邦大師 1 級 ‧ 2019-03-14 01:40:28
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
/images/emoticon/emoticon09.gif

節能減碳愛地球.

-- 另外一招
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)
1
純真的人
iT邦大師 1 級 ‧ 2019-03-14 09:44:48
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

我要發表回答

立即登入回答