やること

小計行のある表を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句で商品ごとに集計する。

商品ごとに集計

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. 小計行ごとに集計

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

小計を出す商品カテゴリごとに集計

-- 商品をカテゴリ分けするための一時テーブル
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. 商品行に行番号を振る。

-- 商品ごとに行番号を振る
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. 小計行に行番号を振る。

-- 行番号
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. 商品行と小計行を一緒に表示する

-- 商品ごとに行番号を振る
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. 商品名を出力する。

-- 商品ごとに行番号を振る
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

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

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

やること

やり方

例

テーブル格納データ

作りたい表

手順

1. 商品ごとに集計

2. 小計行ごとに集計

3. 表に合わせて並び替える

3-1. 表に行番号を振る

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

3-3. 小計行に行番号を振る。

3-4. 商品行と小計行を一緒に表示する

4. 商品名を出力する。