CODEWITHSUNDEEP
sqlsql-servert-sqlcrystal-reports
Alec.
Alec.

Reputation: 5525

Conditional summing T-SQL

This is a really simple question, I know it is. But can't for the life of me figure it out.

I have a few coloumns but the main ones are ReservationFee and Invoice ID.

Basically, there can be multiple "ReservationFees" in a single invoice. This value will be put on a crystal report, so I ideally need to sum up reservation fees for each invoice id.

Example Data

Invoice ID     Reservation Fee
1              200
1              300
2              100
3              350
3              100

Expected Output

Invoice ID     Reservation Fee
1              500
2              100
3              450

I have tried a few different sums and groupings but can't get it right, I'm blaming Monday morning!

Upvotes: 0

Views: 245

Answers (4)

The Hill Boy
The Hill Boy

Reputation: 162

try this one.

declare @Invoice_table table 
(
Invoice_ID int ,     
Reservation_Fee money
)
insert into @Invoice_table values
(1              ,200),
(1              ,300),
(2              ,100),
(3              ,350),
(3              ,100)

SELECT SUM (Reservation_Fee) as Reservation_Fee, Invoice_ID from @Invoice_table group by Invoice_ID

Upvotes: 0

Rowland Shaw
Rowland Shaw

Reputation: 38130

What's wrong with:

SELECT [Invoice Id], SUM([Reservation Fee]) AS [Reservation Fee]
FROM   dbo.YourTable
GROUP BY [Invoice Id]

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

If you want to SUM in the server then:

SELECT [Invoice ID],
       SUM([Reservation Fee]) 
FROM Table 
GROUP BY [Invoice ID]

If you want to SUM in the Crystal then add a command or drag and drop your table

SELECT [Invoice ID], 
       [Reservation Fee]
FROM Table

Then right click in the details section and Select Insert Group.

Add the fields in the details section, Right Click on the Reservation Fee field and select insert running total.

In the window choose a name, select evaluate for each row and Reset on change of group the one you entered before.

Place the newly created field in the Group Footer.

Upvotes: 1

TechDo
TechDo

Reputation: 18649

Try GROUP BY clause:

Select
    [Invoice ID],
    SUM([Reservation Fee]) [Reservation Fee]
From
    YourTable
GROUP BY [Invoice ID]

Upvotes: 0

Related Questions