building sql reports

Question

I’m looking for a report that shows me the bookings summary by location, the data I’m wanting to see is sale amount, Sales order (gm) and Profit %. In a format similar to this

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%

The sale amount will be a sum of all sales, and the same with gross margin for that location. The percentage will be an average of all the profit percentages in that location.

What have you tried? Show us your current attempt. – jarlh Sep 04 '18 at 13:13 — jarlh, Sep 04 '18 at 13:13

score 1 · Accepted Answer · answered Sep 04 '18 at 13:19

You have to use GROUP BY and Aggregate Function

The below query should give you the desired result:

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]

building sql reports

1 Answers1