SQLで小計行のある表を出力する

# やること
小計行のある表をSQLで出力する。

**【小計行のある表の例】**
小計行のある生鮮食品の売り上げ表。表の途中に小計値を出力する行がある。

| 商品       |       売上金額 |    数量    |
|:-----------------|------------------:|------------------:|
| りんご             |        58440 |        725   |
| みかん           |          9256 |       67    |
| ぶどう             |          85024 |       242       |
| 小計（果物）            |            152720 |       1034      |
| ブロッコリー     |           90361 |       330       |
| 小松菜          |      16754 |      359     |
| 小計（野菜）          |  107115     |  689       |

# やり方
商品の売り上げを出力する行と合計値を出力する行をSQLでそれぞれ作成し、表示順に並び替える。

# 例

## テーブル格納データ 
以下の形でテーブルにデータが格納されているとする。

**商品テーブル**

| ItemId   |   ItemName |
|---------:|:--------|
| 1 |  ぶどう   |
| 2 |  小松菜   |
| 3 |   ブロッコリー  |
| 4 |    りんご |
| 5 |    みかん |

**売り上げテーブル**

| SalesDate | ItemId | Number | Amount |
|-----------|-------:|-------:|-------:|
| 2020/5/1  | 1      | 3      | 300    |
| 2020/5/3  | 1      | 2      | 200    |
| 2020/5/7  | 5      | 2      | 800    |
| 2020/5/8  | 2      | 1      | 150    |
| 2020/5/10 | 4      | 1      | 270    |
| 2020/5/12 | 3      | 6      | 1800   |
| 2020/5/15 | 2      | 4      | 600    |
| 2020/5/20 | 3      | 5      | 1500   |
| 2020/5/28 | 4      | 5      | 1350   |


## 作りたい表
作りたい表は以下である。

| 商品名       | 売上金額 | 数量 |
|:------------|---------:|-----:|
| りんご       | 1620     | 6    |
| みかん       | 800      | 2    |
| ぶどう       | 500      | 5    |
| 小計（果物） | 4550     | 21   |
| ブロッコリー | 3300     | 11   |
| 小松菜       | 750      | 5    |
| 小計（野菜） | 2420     | 8    |

## 手順
### 1. 商品ごとに集計
GROUP BY句で商品ごとに集計する。

```sql:商品ごとに集計
SELECT
    Sales.ItemId      AS [商品Id]
   ,SUM(Sales.Amount) AS [売上金額]
   ,SUM(Sales.Number) AS [数量]
FROM
    Sales
GROUP BY
    Sales.ItemId
ORDER BY
    Sales.ItemId ASC
```

**SQLの結果**

| 商品Id | 売上金額 | 数量 |
|--------|----------|------|
| 1      | 500      | 5    |
| 2      | 750      | 5    |
| 3      | 3300     | 11   |
| 4      | 1620     | 6    |
| 5      | 800      | 2    |

### 2. 小計行ごとに集計
今後は、小計行ごとに集計してみる。
テーブルには小計行のキーとなる商品カテゴリなどの列がないので、新たに一時テーブルを作り、商品カテゴリごとに商品を分類してから集計することにする。

```sql:小計を出す商品カテゴリごとに集計
-- 商品をカテゴリ分けするための一時テーブル
DECLARE @ItemCategory TABLE(
    Category Int NOT NULL,
    ItemId Int NOT NULL
)

-- 果物(1)と野菜(2)で商品をカテゴリ分けする。
INSERT INTO @ItemCategory VALUES (1,4),(1,5),(1,1),(2,2),(2,3)

SELECT
    ItemCategory.Category AS [小計]
   ,SUM(Sales.Amount)     AS [売上金額]
   ,SUM(Sales.Number)     AS [数量]
FROM
	Sales
	INNER JOIN @ItemCategory AS ItemCategory ON ItemCategory.ItemId = Sales.ItemId
GROUP BY
	ItemCategory.Category
ORDER BY
	ItemCategory.Category
```

**SQLの結果**

| 小計 | 売上金額 | 数量 |
|------|----------|------|
| 1    | 2920     | 13   |
| 2    | 4050     | 16   |

### 3. 表に合わせて並び替える
表の出力順はテーブル上にデータとしてないので、こちらも一時テーブルで自作しよう。
先ほど書いた2つのSQLを書き直し、出力順が分かるようにする。

#### 3-1. 表に行番号を振る
出力順を制御するにあたり、表に行番号を振る。

| 行番号 | 商品       |       売上金額 |    数量    |
|---:|:-----------------|------------------:|------------------:|
| 1| りんご             |        58440 |        725   |
| 2| みかん           |          9256 |       67    |
| 3| ぶどう             |          85024 |       242       |
| 4| 小計（果物）            |            152720 |       1034      |
| 5| ブロッコリー     |           90361 |       330       |
| 6| 小松菜          |      16754 |      359     |
| 7| 小計（野菜）          |  107115     |  689       |

#### 3-2. 商品行に行番号を振る。

```sql
-- 商品ごとに行番号を振る
DECLARE @rowNumberItem TABLE(
    rowNumber Int NOT NULL,
    ItemCD Int NOT NULL
)
INSERT INTO @rowNumberItem VALUES (1,4),(2,5),(3,1),(5,3),(6,2)

SELECT
	rowNumber.rowNumber AS [行番号]
   ,Items.*
FROM
	(
	SELECT
		Sales.ItemId       AS [商品Id]
	   ,SUM(Sales.Amount)  AS [売上金額]
	   ,SUM(Sales.Number)  AS [数量]
	FROM
		Sales
	GROUP BY
		Sales.ItemId
	) AS Items
	INNER JOIN @rowNumberItem AS rowNumber ON rowNumber.ItemCD = Items.[商品Id]
ORDER BY
	[行番号] ASC
```

**SQLの結果**

| 行番号 | 商品Id | 売上金額 | 数量 |
|-------:|-------:|--------:|-----:|
| 1      | 4      | 1620    | 6    |
| 2      | 5      | 800     | 2    |
| 3      | 1      | 500     | 5    |
| 5      | 3      | 3300    | 11   |
| 6      | 2      | 750     | 5    |

#### 3-3. 小計行に行番号を振る。
```sql
-- 行番号
DECLARE @rowNumberSubTotal TABLE(
    rowNumber Int NOT NULL,
    Category Int NOT NULL
)
INSERT INTO @rowNumberSubTotal VALUES (4,1),(7,2)

-- 商品をカテゴリ分けするための一時テーブル
DECLARE @ItemCategory TABLE(
    Category Int NOT NULL,
    ItemId Int NOT NULL
)

-- 果物(1)と野菜(2)で商品をカテゴリ分けする。
INSERT INTO @ItemCategory VALUES (1,4),(1,5),(1,1),(2,2),(2,3)

SELECT
	rowNumber.rowNumber AS [行番号],
	Subtotal.*
FROM
	(
	SELECT
		ItemCategory.Category AS [小計]
	   ,SUM(Sales.Amount)     AS [売上金額]
	   ,SUM(Sales.Number)     AS [数量]
	FROM
		Sales
		INNER JOIN @ItemCategory AS ItemCategory ON ItemCategory.ItemId = Sales.ItemId
	GROUP BY
		ItemCategory.Category
	) AS Subtotal
	INNER JOIN @rowNumberSubTotal AS rowNumber ON rowNumber.Category = Subtotal.小計
```

**SQLの結果**

| 行番号 | 小計 | 売上金額 | 数量 |
|--------|------|----------|------|
| 4      | 1    | 2920     | 13   |
| 7      | 2    | 4050     | 16   |

#### 3-4. 商品行と小計行を一緒に表示する
```sql
-- 商品ごとに行番号を振る
DECLARE @rowNumberItem TABLE(
    rowNumber Int NOT NULL,
    ItemCD Int NOT NULL
)
INSERT INTO @rowNumberItem VALUES (1,4),(2,5),(3,1),(5,3),(6,2)

-- 小計行ごとに行番号を振る
DECLARE @rowNumberSubTotal TABLE(
    rowNumber Int NOT NULL,
    Category Int NOT NULL
)
INSERT INTO @rowNumberSubTotal VALUES (4,1),(7,2)

-- 商品をカテゴリ分けするための一時テーブル
DECLARE @ItemCategory TABLE(
    Category Int NOT NULL,
    ItemId Int NOT NULL
)

-- 果物(1)と野菜(2)で商品をカテゴリ分けする。
INSERT INTO @ItemCategory VALUES (1,4),(1,5),(1,1),(2,2),(2,3)

-- 商品
SELECT
	rowNumber.rowNumber AS [行番号]
   ,Items.[売上金額]
   ,Items.[数量]
FROM
	(
	SELECT
		Sales.ItemId       AS [商品Id]
	   ,SUM(Sales.Amount)  AS [売上金額]
	   ,SUM(Sales.Number)  AS [数量]
	FROM
		Sales
	GROUP BY
		Sales.ItemId
	) AS Items
	INNER JOIN @rowNumberItem AS rowNumber ON rowNumber.ItemCD = Items.[商品Id]
-- 小計
UNION ALL
SELECT
	rowNumber.rowNumber AS [行番号]
	,Subtotal.[売上金額]
	,Subtotal.[数量]
FROM
(
	SELECT
		ItemCategory.Category AS [小計]
	   ,SUM(Sales.Amount)     AS [売上金額]
	   ,SUM(Sales.Number)     AS [数量]
	FROM
		Sales
		INNER JOIN @ItemCategory AS ItemCategory ON ItemCategory.ItemId = Sales.ItemId
	GROUP BY
		ItemCategory.Category
	) AS Subtotal
	INNER JOIN @rowNumberSubTotal AS rowNumber ON rowNumber.Category = Subtotal.小計
ORDER BY
	[行番号] ASC
```

**SQLの結果**

| 行番号 | 売上金額 | 数量 |
|--------|----------|------|
| 1      | 1620     | 6    |
| 2      | 800      | 2    |
| 3      | 500      | 5    |
| 4      | 2920     | 13   |
| 5      | 3300     | 11   |
| 6      | 750      | 5    |
| 7      | 4050     | 16   |

### 4. 商品名を出力する。

```sql
-- 商品ごとに行番号を振る
DECLARE @rowNumberItem TABLE(
    rowNumber Int NOT NULL,
    ItemCD Int NOT NULL
)
INSERT INTO @rowNumberItem VALUES (1,4),(2,5),(3,1),(5,3),(6,2)

-- 小計行ごとに行番号を振る
DECLARE @rowNumberSubTotal TABLE(
    rowNumber Int NOT NULL,
    Category Int NOT NULL
)
INSERT INTO @rowNumberSubTotal VALUES (4,1),(7,2)

-- カテゴリテーブル
DECLARE @Category TABLE(
    Category Int NOT NULL,
    CategoryName Varchar(50) NOT NULL
)
INSERT INTO @Category VALUES (1,'小計（果物）'),(2,'小計（野菜）')

-- 商品をカテゴリ分けするための一時テーブル
DECLARE @ItemCategory TABLE(
    Category Int NOT NULL,
    ItemId Int NOT NULL
)

-- 果物(1)と野菜(2)で商品をカテゴリ分けする。
INSERT INTO @ItemCategory VALUES (1,4),(1,5),(1,1),(2,2),(2,3)

-- 商品
SELECT
	rowNumber.rowNumber AS [行番号]
   ,Item.ItemName AS [商品名]
   ,Items.[売上金額]
   ,Items.[数量]
FROM
	(
	SELECT
		Sales.ItemId       AS [商品Id]
	   ,SUM(Sales.Amount)  AS [売上金額]
	   ,SUM(Sales.Number)  AS [数量]
	FROM
		Sales
	GROUP BY
		Sales.ItemId
	) AS Items
	INNER JOIN @rowNumberItem AS rowNumber ON rowNumber.ItemCD = Items.[商品Id]
	INNER JOIN Item ON Item.ItemId = Items.[商品Id]
-- 小計
UNION ALL
SELECT
	rowNumber.rowNumber AS [行番号]
   ,Category.CategoryName AS [商品名]
   ,Subtotal.[売上金額]
   ,Subtotal.[数量]
FROM
(
	SELECT
		ItemCategory.Category AS [小計]
	   ,SUM(Sales.Amount)     AS [売上金額]
	   ,SUM(Sales.Number)     AS [数量]
	FROM
		Sales
		INNER JOIN @ItemCategory AS ItemCategory ON ItemCategory.ItemId = Sales.ItemId
	GROUP BY
		ItemCategory.Category
	) AS Subtotal
	INNER JOIN @rowNumberSubTotal AS rowNumber ON rowNumber.Category = Subtotal.小計
	INNER JOIN @Category AS Category ON Category.Category = Subtotal.小計
ORDER BY
	 [行番号] ASC
```

**SQLの結果**

| 行番号 | 商品名       | 売上金額 | 数量 |
|--------|--------------|----------|------|
| 1      | りんご       | 1620     | 6    |
| 2      | みかん       | 800      | 2    |
| 3      | ぶどう       | 500      | 5    |
| 4      | 小計（果物） | 2920     | 13   |
| 5      | ブロッコリー | 3300     | 11   |
| 6      | 小松菜       | 750      | 5    |
| 7      | 小計（野菜） | 4050     | 16   |

目的とする表を出力できた。