SQL語法如何計算數量

sql語法如何計算數量

yu0901 2022-06-01 15:21:10 ‧ 1213 瀏覽

分享至

請教各位大神SQL語法如下
select BRAND_NAME 它牌 ,COUNT (ASSET.BRAND_NAME)from ASSET
where BRAND_NAME not in ('brother','NCC')
GROUP BY BRAND_NAME

若需要計算所有數量該如何下???

登入發表討論

直播研討會

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

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2022-06-01 17:49:06

最佳解答

這樣??

declare @ASSET table(
	BRAND_NAME nvarchar(50)
)
insert into @ASSET
values('')
,('')
,('')
,('')
,('')
,('baby lock')
,('baby lock')
,('baby lock')
,('baby lock')
,('baby lock')
,('baby lock')


select BRAND_NAME 它牌 
,COUNT (0) Num
,sum(COUNT (0))over() TotalNum
from @ASSET
where BRAND_NAME not in ('brother','NCC')
GROUP BY BRAND_NAME

或這樣??

select *
from ((
	select BRAND_NAME 它牌 
	,COUNT (0) Num
	from @ASSET
	where BRAND_NAME not in ('brother','NCC')
	GROUP BY BRAND_NAME
)union all(
	select '總計'
	,count(0) Num
	from @ASSET
	where BRAND_NAME not in ('brother','NCC')
)) k

回應 7
分享
檢舉

看更多先前的回應...收起先前的回應...

yu0901 iT邦新手 4 級 ‧ 2022-06-01 18:07:34 檢舉

您好感謝回復
請教若用此方式裡面共有9筆資料需要算總計
我該如何下?

純真的人 iT邦大師 1 級 ‧ 2022-06-01 18:10:49 檢舉

那就是這個答案吧~
就二次查詢也可以

select *
from ((
	select BRAND_NAME 它牌 
	,COUNT (0) Num
	from @ASSET
	where BRAND_NAME not in ('brother','NCC')
	GROUP BY BRAND_NAME
)union all(
	select '總計'
	,count(0) Num
	from @ASSET
	where BRAND_NAME not in ('brother','NCC')
)) k

或這樣

select Sum(Num) 總計
from (
	select BRAND_NAME 它牌 
	,COUNT (0) Num
	from @ASSET
	where BRAND_NAME not in ('brother','NCC')
	GROUP BY BRAND_NAME
) k

yu0901 iT邦新手 4 級 ‧ 2022-06-01 18:34:01 檢舉

感謝@純真的人^^
還是不對真不知該如何修改

yu0901 iT邦新手 4 級 ‧ 2022-06-01 18:40:41 檢舉

必須宣告變數

純真的人 iT邦大師 1 級 ‧ 2022-06-01 19:41:28 檢舉

那個@是我宣告臨時變數~你直接刪除~
像這樣~

select *
from ((
	select BRAND_NAME 它牌 
	,COUNT (0) Num
	from ASSET
	where BRAND_NAME not in ('brother','NCC')
	GROUP BY BRAND_NAME
)union all(
	select '總計'
	,count(0) Num
	from ASSET
	where BRAND_NAME not in ('brother','NCC')
)) k

select Sum(Num) 總計
from (
	select BRAND_NAME 它牌 
	,COUNT (0) Num
	from ASSET
	where BRAND_NAME not in ('brother','NCC')
	GROUP BY BRAND_NAME
) k

純真的人 iT邦大師 1 級 ‧ 2022-06-01 19:42:58 檢舉

除非你有像我這樣宣告@ASSET才有用~

yu0901 iT邦新手 4 級 ‧ 2022-06-02 10:51:55 檢舉

讚!厲害
我自己寫這樣

SELECT     BRAND_NAME AS 它牌, COUNT(BRAND_NAME) AS AA
FROM         ASSET 
WHERE      (BRAND_NAME NOT IN ('brother', 'NCC'))
GROUP BY BRAND_NAME
union
SELECT     '', COUNT(BRAND_NAME) AS AA
FROM         ASSET 
WHERE      (BRAND_NAME NOT IN ('brother', 'NCC'))

但你的更漂亮
讚

登入發表回應

Zed_Yang

iT邦新手 3 級 ‧ 2022-06-01 15:27:33

select A.BRAND_NAME 它牌 ,COUNT (A.BRAND_NAME)
        ,B.AllCnt
from ASSET A
JOIN (
        SELECT COUNT(1) AllCnt
          FROM ASSET
        ) B ON 1 = 1
where A.BRAND_NAME not in ('brother','NCC')
GROUP BY A.BRAND_NAME

select A.BRAND_NAME 它牌 ,COUNT (A.BRAND_NAME)
        , (SELECT COUNT(1) FROM ASSET) AS AllCnt
from ASSET A
where A.BRAND_NAME not in ('brother','NCC')
GROUP BY A.BRAND_NAME

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

yu0901 iT邦新手 4 級 ‧ 2022-06-01 15:44:35 檢舉

您好感謝回復 count後面無法帶簡碼

Zed_Yang iT邦新手 3 級 ‧ 2022-06-01 15:47:07 檢舉

select A.BRAND_NAME 它牌 ,COUNT (A.BRAND_NAME)
        ,B.AllCnt
from ASSET A
JOIN (
        SELECT COUNT(1) AllCnt
          FROM ASSET
        ) B ON 1 = 1
where A.BRAND_NAME not in ('brother','NCC')
GROUP BY A.BRAND_NAME