CODEWITHSUNDEEP
sqlinfomaker
SoTech
SoTech

Reputation: 41

building sql reports

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.

Upvotes: 1

Views: 72

Answers (1)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

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]

Upvotes: 1

Related Questions