iT邦幫忙

0

SQL語法如何計算數量

  • 分享至 

  • xImage

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

https://ithelp.ithome.com.tw/upload/images/20220601/201486376WfpFb5nP1.png
若需要計算所有數量該如何下???

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

0
純真的人
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

https://ithelp.ithome.com.tw/upload/images/20220601/20061369SQsSqIPV8m.png

或這樣??

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

https://ithelp.ithome.com.tw/upload/images/20220601/20061369b8ZCqBeIwa.png

看更多先前的回應...收起先前的回應...
yu0901 iT邦新手 4 級 ‧ 2022-06-01 18:07:34 檢舉

您好感謝回復
請教若用此方式裡面共有9筆資料需要算總計
我該如何下?https://ithelp.ithome.com.tw/upload/images/20220601/201486379xTdLcJlZ2.png

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

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 檢舉

感謝@純真的人^^
還是不對真不知該如何修改https://ithelp.ithome.com.tw/upload/images/20220601/20148637oh6GZZBIL6.png

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

必須宣告變數https://ithelp.ithome.com.tw/upload/images/20220601/20148637BJJ8Gbc0Vf.png

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

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


除非你有像我這樣宣告@ASSET才有用~
https://ithelp.ithome.com.tw/upload/images/20220601/20061369cI27uHot6n.png

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'))

但你的更漂亮

0
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
看更多先前的回應...收起先前的回應...
yu0901 iT邦新手 4 級 ‧ 2022-06-01 15:44:35 檢舉

您好感謝回復 count後面無法帶簡碼
https://ithelp.ithome.com.tw/upload/images/20220601/20148637ThwmwNC6Bm.png

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
yu0901 iT邦新手 4 級 ‧ 2022-06-01 15:55:26 檢舉

不好意思,感謝您 我SQL語法還沒那麼熟
錯誤訊息如下
資料行 'B.AllCnt' 在選取清單中無效,因為它並未包含在彙總函式或 GROUP BY 子句中。
https://ithelp.ithome.com.tw/upload/images/20220601/201486377ZDHXBlrKT.png

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

GROUP BY 他說缺少的部分也加上即可

yu0901 iT邦新手 4 級 ‧ 2022-06-01 16:05:16 檢舉

感謝請問94031是什麼意思
https://ithelp.ithome.com.tw/upload/images/20220601/20148637a038eHGv5O.png

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

我該如何計算數量縱列所有加總用SUM語法也不對
https://ithelp.ithome.com.tw/upload/images/20220601/20148637JEJihjQp3Z.png

我要發表回答

立即登入回答