iT邦幫忙

0

SQL 語法 垂直欄位的加總計算

  • 分享至 

  • xImage

請問各位大神 請問一個基本SQL運算問題

category_id     data_value
     1          174.2583	
     2          1537.2326	
     3          141.1724	
     4          35.3124	

請問要如何算出
category_id=1 and category_id=2 的 data_value 加總
category_id=3 and category_id=4 的 data_value 加總

ckp6250 iT邦好手 1 級 ‧ 2022-11-10 16:47:10 檢舉
category_id 只有1,2,3,4,這四個值嗎?
不會再有其它的?
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
實習工程師
iT邦新手 1 級 ‧ 2022-11-10 16:50:52
CREATE TABLE #TABLE(
	category_id INT
	,data_value FLOAT
)

INSERT INTO #TABLE(category_id,data_value)Values(1,174.2583)
INSERT INTO #TABLE(category_id,data_value)Values(2,1537.2326)
INSERT INTO #TABLE(category_id,data_value)Values(3,141.1724)
INSERT INTO #TABLE(category_id,data_value)Values(4,35.3124)


/*方法1*/
SELECT 
	SUM(data_value) AS GROUP_A
FROM #TABLE
WHERE  (category_id=1 OR category_id=2 )


SELECT 
	SUM(data_value) AS GROUP_A
FROM #TABLE
WHERE  (category_id=3 OR category_id=4 )

/*方法2*/
SELECT 
	(
		SELECT 
			SUM(data_value) AS GROUP_A
		FROM #TABLE
		WHERE  (category_id=1 OR category_id=2 )
	) GROUP_A
	, 
	(
		SELECT 
			SUM(data_value) AS GROUP_B
		FROM #TABLE
		WHERE  (category_id=3 OR category_id=4 )
	
	) GROUP_B



DROP TABLE #TABLE
2
johncoc
iT邦新手 3 級 ‧ 2022-11-10 18:07:29

先假設你要每兩個相加

SELECT (category_id-1)/2,SUM(data_value)
FROM t
GROUP BY (category_id-1)/2
1
OuJiaHao
iT邦新手 4 級 ‧ 2022-11-13 20:28:15
0
JamesDoge
iT邦高手 1 級 ‧ 2023-02-15 10:14:41

你可以使用 GROUP BY 來將相同的 category_id 分組,然後使用聚合函數 SUM 加總對應的 data_value。

下面是一個範例 SQL 語句:

SELECT
  SUM(CASE WHEN category_id IN (1, 2) THEN data_value ELSE 0 END) AS sum_1_2,
  SUM(CASE WHEN category_id IN (3, 4) THEN data_value ELSE 0 END) AS sum_3_4
FROM your_table;

我要發表回答

立即登入回答