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

sql distinct

tlcric 2019-03-13 16:33:12 ‧ 5289 瀏覽

分享至

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;

看更多先前的討論...收起先前的討論...

純真的人 iT邦大師 1 級 ‧ 2019-03-13 16:48:13 檢舉

可以出個一些資料表的資料~表示你的方式~跟想要的答案嗎?

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
我想在後面再加一個總計

純真的人 iT邦大師 1 級 ‧ 2019-03-14 09:59:55 檢舉

一樣是union all顯示即可~

tlcric iT邦新手 5 級 ‧ 2019-03-14 10:55:16 檢舉

用union all就可以得到我要的結果了，謝謝大大

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

4 個回答

張小馬~

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

【再加一列於最底下】和【再加一欄於最右邊】的差異~

回應 3
分享
檢舉

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

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

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

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

張小馬~ iT邦新手 3 級 ‧ 2019-03-14 17:17:26 檢舉

了解，已更新回答~

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 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

回應
分享
檢舉

登入發表回應

一級屠豬士

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)

回應 3
分享
檢舉

海綿寶寶 iT邦大神 1 級 ‧ 2019-03-14 08:54:59 檢舉

題目又沒有要 abs, 為什麼要送 abs

一級屠豬士 iT邦大師 1 級 ‧ 2019-03-14 16:42:40 檢舉

節能減碳愛地球.

一級屠豬士 iT邦大師 1 級 ‧ 2019-03-14 23:00:40 檢舉

-- 另外一招
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)

登入發表回應

純真的人

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