CODEWITHSUNDEEP
sql-server-2016
Femmer
Femmer

Reputation: 132

SQL How to subtract from only the MIN value

My query is pretty simple:

SELECT SUM(rate), SUM(nights), SUM(rate) * SUM(nights) AS subtotal, (SUM(rate) * SUM(nights)) * MIN(tax) AS tax,
   SUM(adults), SUM(fee), ((SUM(rate) * SUM(nights)) * MIN(tax)) + SUM(fee) AS total
    FROM table
        WHERE group = @group_code
            GROUP BY rate

The results of my query:

rate   nights   subtotal      tax     adults     fee      total
  0.00   14         0.00      0.00       21      105      105.00
154.00  226     34804.00   5373.04      141      705    40882.04
254.00    6      1524.00    235.27        4       20     1779.27

I want to be able to subtract 2 adults from whatever the MIN(rate) row, in this case the row with a rate of $0.00, but leave the other rows alone.

Anyone able to help?

Upvotes: 0

Views: 170

Answers (1)

avb
avb

Reputation: 1753

If I understood properly this would do the trick:

with s as (
    select
        SUM(rate) rate,
        SUM(nights) nights,
        SUM(rate) * SUM(nights) subtotal,
        (SUM(rate) * SUM(nights)) * MIN(tax) tax,
        SUM(adults) adults,
        SUM(fee) fee,
        ((SUM(rate) * SUM(nights)) * MIN(tax)) + SUM(fee) total,
    from table
    where group = @group_code
    group by rate
), sorted as (
    select s.rate, s.nights, s.subtotal, s.tax, s.adults, s.fee, s.total, ROW_NUMBER() over(order by s.rate) lp
    from s
)
select sorted.rate,
    sorted.nights,
    sorted.subtotal,
    sorted.tax,
    sorted.adults + IIF(lp = 1, -2, 0),
    sorted.fee,
    sorted.total
from sorted

Upvotes: 1

Related Questions