生成 SQL 报表

Question

我正在寻找一份报告，该报告按位置显示预订摘要，我想查看的数据是销售金额，销售订单(gm(和利润%。 格式与此类似

Sales Orders - Source Loc ID    Sales Orders - Sales Location   Sale Amt    Sales Orders - Gross Margin$    Sales Orders - Gross Margin%    Profit%(p21)
10  Mobile  $1,892.00   $473.00     25.00%  25.00%
20  Louisiana   $9,834.90   $1,966.97   20.00%  0.00%
30  Florida $3,547.32   $1,324.01   37.30%  0.00%
40  Birmingham  $328.48     $31.65  9.60%   0.00%
50  Northeast   $20,336.52  $4,067.30   20.00%  0.00%

销售金额将是所有销售额的总和，与该位置的毛利率相同。 该百分比将是该位置所有利润百分比的平均值。

Answer 1

你必须使用GROUP BY和聚合函数

以下查询应提供所需的结果：

SELECT [Sales Orders - Source Loc ID],[Sales Orders - Sales Location],
SUM([Sale Amt]) AS [Sale Amt],
SUM([Sales Orders - Gross Margin$]) AS [Sales Orders - Gross Margin$],
AVG([Sales Orders - Gross Margin%]) AS [Sales Orders - Gross Margin%],
AVG([Profit%(p21)]) AS [Profit%(p21)]
FROM Table
GROUP BY [Sales Orders - Source Loc ID],[Sales Orders - Sales Location]